-
Nov 30th, 2022, 08:41 AM
#1
Thread Starter
PowerPoster
SQL Server Query - count if any rows have a certain value
I'm trying to write a query and will continue to try but am posting it here to get help as well.
I need to look at a group of rows and see if any of them has a column equal to a certain value.
The key is SONumber.
I want to return one row of the 6 in the resultset in this attachment.
Because one of those six rows is disposition = 'S' I want to return a column with a default value of zero.
If none of the rows has a disposition of 'S', I want to return the EXTPRICE value of EQ_PORT, 24835.30 in said column.
I greatly simplied my query to make it easier to understand (and I try to always start simple and build), so let me know if this makes sense or if you need more info.
I appreciate your help!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 08:43 AM
#2
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
And now that i am looking at this, I probably gave it a stupid title. At first I was thinking I wanted to count the S's and if > 0, return an amount of zero. If EQ 0, return EXTPRICE of the ITEM = EQ_PORT row. I'm not sure if that would be a COUNT or not.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 09:14 AM
#3
Re: SQL Server Query - count if any rows have a certain value
Get all the EQ_PORTs ...
Code:
select SONumber, EXTPRICE where disposition = 'EQ_PORT'
Get the "S" rows:
Code:
select SONumber, EXTPRice where disposition = 'S'
And combine:
Code:
select T.SONumber, case when S.EXTPRICE is not null then 0 else EP.EXTPRICE end as EXTPRICE
from table T
left join (select SONumber, EXTPRice where disposition = 'S') S on T.SONumber = S.SONumber
left join (select SONumber, EXTPRICE where disposition = 'EQ_PORT') EP on T.SONumber = EP.SONumber
-tg
-
Nov 30th, 2022, 09:28 AM
#4
Re: SQL Server Query - count if any rows have a certain value
Tested in SQLite.
Syntax might differ to SQL-Server
Code:
SELECT M1.SONumber, IIF(M2.CountS>0,0,M3.EXTPRICE) AS Result FROM tbl_mock AS M1
LEFT JOIN
(SELECT SONumber, COUNT(disposition) AS CountS FROM tbl_mock WHERE disposition='S' GROUP BY SONumber) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN tbl_mock AS M3
ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'
GROUP BY M1.SONumber
Last edited by Zvoni; Nov 30th, 2022 at 10:24 AM.
Reason: GROUP BY in first LEFT JOIN missing as pointed out by wqweto
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 30th, 2022, 09:32 AM
#5
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by techgnome
Get all the EQ_PORTs ...
Code:
select SONumber, EXTPRICE where disposition = 'EQ_PORT'
Get the "S" rows:
Code:
select SONumber, EXTPRice where disposition = 'S'
And combine:
Code:
select T.SONumber, case when S.EXTPRICE is not null then 0 else EP.EXTPRICE end as EXTPRICE
from table T
left join (select SONumber, EXTPRice where disposition = 'S') S on T.SONumber = S.SONumber
left join (select SONumber, EXTPRICE where disposition = 'EQ_PORT') EP on T.SONumber = EP.SONumber
-tg
eh... No.... EQ_Port is in Column ITEM
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 30th, 2022, 10:10 AM
#6
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by Zvoni
eh... No.... EQ_Port is in Column ITEM
Can I claim temporary insanity as I hadn't had my coffee yet? Still ... it should work with that tweak.
-tg
-
Nov 30th, 2022, 10:15 AM
#7
Re: SQL Server Query - count if any rows have a certain value
This
Code:
(SELECT SONumber, COUNT(disposition) AS CountS FROM tbl_mock WHERE disposition='S') AS M2
. . . is missing a GROUP BY SONumber
This
Code:
LEFT JOIN tbl_mock AS M3
ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'
. . . might return more than one row. It's not clear as table schema is missing.
I would defensively guard against duplicate EQ_PORT rows for the same SONumber i.e. add GROUP BY SONumber and take MAX(M3.EXTPRICE) in a derived table.
cheers,
</wqw>
-
Nov 30th, 2022, 10:23 AM
#8
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by wqweto
This
Code:
(SELECT SONumber, COUNT(disposition) AS CountS FROM tbl_mock WHERE disposition='S') AS M2
. . . is missing a GROUP BY SONumber
Agreed. Missed that one (SQlite doesn't complain about such stuff).
This
Code:
LEFT JOIN tbl_mock AS M3
ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'
. . . might return more than one row. It's not clear as table schema is missing.
I would defensively guard against duplicate EQ_PORT rows for the same SONumber i.e. add GROUP BY SONumber and take MAX(M3.EXTPRICE) in a derived table.
cheers,
</wqw>
Agreed. But as you said: Table_Schema would explain it.
As i understood it, there can be only one row with ITEM='EQ_Port', so there is probably a combined UNIQUE-Constraint consisting of SONumber and ITEM
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 30th, 2022, 11:31 AM
#9
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by Zvoni
. . . so there is probably a combined UNIQUE-Constraint consisting of SONumber and ITEM
Could be but don't count on it :-)))
cheers,
</wqw>
-
Nov 30th, 2022, 11:37 AM
#10
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
Thanks, I love you guys, though it bothers me I could not do my job without you!
So I took tg's query and turned it into this:
Code:
select T.SONumber, case when S.EXTPRICE is not null then 0 else EP.EXTPRICE end as EXTPRICE
from Jobs T
join xqryP21LinesRelated p21 on p21.SONO = T.SONumber
left join
(select SONumber, EXTPRice
from Jobs T
join xqryP21LinesRelated p21 on p21.SONO = T.SONumber
where disposition = 'S') S on T.SONumber = S.SONumber
left join
(select SONumber, EXTPRICE
from Jobs T
join xqryP21LinesRelated p21 on p21.SONO = T.SONumber
where ITEM = 'EQ_PORT') EP on T.SONumber = EP.SONumber
WHERE T.SONumber = '1066152'
And I belive that looks good. I am running one SONumber at a time to take a look at the results. That query returns me 6 rows that are duplicates (EXTPRICE always zero) so I can just throw a distinct at the results.
My next one returned me 23 duplicate rows where EXPRICE is always null and that is because there is no ITEM = EQ_PORT in the results. Again, just need a DISTINCT.
The next one returned me 180 rows. 90 are duplicates with each other. That is because there is more than one ITEM = EQ_PORT; there are two, so two sets. I have to ask my users if they want EXTPRICE added together if more than one.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 12:19 PM
#11
Re: SQL Server Query - count if any rows have a certain value
Have you tried my solution?
adding the sum of EXTPRICE in my query is not black magic
btw: your query doesn‘t correspond with your task.
“if there is a line with S in disposition return 0 otherwise return EXTPRICE where EQ_PORT in ITEM“
i don‘t see you evaluating the count of S
Last edited by Zvoni; Nov 30th, 2022 at 12:22 PM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 30th, 2022, 12:23 PM
#12
Re: SQL Server Query - count if any rows have a certain value
There wasn't a _need_ to count the number of S rows ... just that at least ONE exists. If one does, then the price is 0... otherwise, get the price from the EQ_PORT row.
-tg
-
Nov 30th, 2022, 12:25 PM
#13
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by techgnome;[URL="tel:5587642"
5587642[/URL]]There wasn't a _need_ to count the number of S rows ... just that at least ONE exists. If one does, then the price is 0... otherwise, get the price from the EQ_PORT row.
-tg
Ok, missed that.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Nov 30th, 2022, 12:48 PM
#14
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
So the requirements for this have taken another turn and honestly I don't think I have the sql skills for this and I do not expect to hand it over to you guys 100%. I thought all I needed was some guidance to get started. Now they are telling me that if there are no ITEM = EQ_PORT rows that i need to add all items with a disposition = 'A'. I said I would like to write a c# program to do this because I can't do it in a query. If they aboslutely need a query, maybe I can hire one of you so you at least get more compensation than a thank you!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 12:53 PM
#15
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
Ugh, I forget that vbForums doesn't email you that you received replies and I neglected to refresh this and see those past few posts.
Requirements for this are trickling in. Let me see if I can summarize what i need to do, but as I said the sql to do it may be beyond me.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 12:57 PM
#16
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
@zvoni - My apologies for not trying your solution. I tried tg's and it was going the right way. I didn't see the need for two. I will take a look at yours now.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 01:04 PM
#17
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
@zvoni, I got this far but the problem which no one else could've know about because I did not give you guys the full schema is that Jobs.SONumber has be joined to xqryP21LinesRelated.SONO and it is in the xqry view where Disposition is defined. So I don't know how to tweak the 2nd part of your query or perhaps the fact that it is not the same table changes the query.
Code:
-- zvoni's way:
SELECT M1.SONumber, IIF(M2.CountS>0,0,M3.EXTPRICE) AS Result
FROM Jobs AS M1
LEFT JOIN
(SELECT SONumber, COUNT(disposition) AS CountS
FROM Jobs
join xqryP21LinesRelated p21 on p21.SONO = Jobs.SONumber
WHERE disposition='S'
GROUP BY SONumber) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN tbl_mock AS M3
ON M3.SONumber=M1.SONumber AND M3.ITEM='EQ_PORT'
GROUP BY M1.SONumber
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Nov 30th, 2022, 04:22 PM
#18
Re: SQL Server Query - count if any rows have a certain value
That‘s the part i don‘t understand:
in which table is disposition now? In jobs or in this P21?
edit: ah, ok, got it.
just qualify disposition with p21
….. p21.disposition inside the subquery
and the change in requirements #14 don‘t sound too complicated
i‘ll have a look tomorrow, since it’s late in the evening in Germany
Last edited by Zvoni; Nov 30th, 2022 at 04:27 PM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 1st, 2022, 04:09 AM
#19
Re: SQL Server Query - count if any rows have a certain value
OK, i've cleaned up my code a bit
I'm assuming EXTPRICE and ITEM are a Columns of Jobs.
If it's somewhere else, do tell.
Code:
-- zvoni's way:
SELECT M1.SONumber, IIF(M2.CountS>0,0,M3.EXTPRICE) AS Result
FROM Jobs AS M1
LEFT JOIN
(SELECT J1.SONumber, COUNT(P21.disposition) AS CountS
FROM Jobs AS J1
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J1.SONumber
WHERE P21.disposition='S'
GROUP BY J1.SONumber) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN
(SELECT J2.SONumber, J2.EXTPRICE
FROM Jobs AS J2
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
WHERE J2.ITEM='EQ_PORT') AS M3
ON M3.SONumber=M1.SONumber
GROUP BY M1.SONumber
And now what's this with "If no EQ_PORTS in ITEM" add what of disposition='A''?
EDIT: Extended with that "Add Items with Disposition='A'
I had to guess, which Fields to sum up (EXTPRICE here)
Code:
-- zvoni's way:
SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice) AS Result
FROM Jobs AS M1
LEFT JOIN
(SELECT J1.SONumber, COUNT(P21.disposition) AS CountS
FROM Jobs AS J1
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J1.SONumber
WHERE P21.disposition='S'
GROUP BY J1.SONumber) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN
(SELECT J2.SONumber, J2.EXTPRICE
FROM Jobs AS J2
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
WHERE J2.ITEM='EQ_PORT') AS M3
ON M3.SONumber=M1.SONumber
LEFT JOIN
(SELECT J3.SONumber, SUM(J3.EXTPRICE) AS SumPrice
FROM Jobs AS J3
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J3.SONumber
WHERE P21.disposition='A'
GROUP BY J3.SONumber) AS M4
ON M4.SONumber=M1.SONumber
GROUP BY M1.SONumber
EDIT2: Just looked at it again, and i might see some points to optimize the query, but it would depend, in which table/view which column is.
Like what you said, that column "disposition" actually comes from P21.
I would need to know, where EXTPRICE and ITEM are located
Code:
-- zvoni's way:
SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result
FROM Jobs AS M1
LEFT JOIN
/*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
(SELECT P21.SONO AS SONumber, COUNT(P21.disposition) AS CountS
FROM xqryP21LinesRelated AS P21
WHERE P21.disposition='S'
GROUP BY P21.SONO) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN
--Alternative if you want a single row for multiple lines with EQ_PORT with EXTPRICE summed up, or use any other Aggregate-Function: MAX, MIN, AVG whatever
--(SELECT J2.SONumber, SUM(J2.EXTPRICE) AS EXTPRICE
--FROM Jobs AS J2
--INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
--WHERE J2.ITEM='EQ_PORT'
--GROUP BY J2.SONumber) AS M3
(SELECT J2.SONumber, J2.EXTPRICE
FROM Jobs AS J2
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
WHERE J2.ITEM='EQ_PORT') AS M3
ON M3.SONumber=M1.SONumber
LEFT JOIN
(SELECT J3.SONumber, SUM(J3.EXTPRICE) AS SumPrice
FROM Jobs AS J3
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J3.SONumber
WHERE P21.disposition='A'
GROUP BY J3.SONumber) AS M4
ON M4.SONumber=M1.SONumber
GROUP BY M1.SONumber
and wqweto's Question is still valid: Is it possible to have more than 1 line with ITEM=EQ_PORT per SONumber?
EDIT: OK, just saw that you answered that in Post #10
EDIT again:
Explanation how it works:
Since we're dealing with LEFT JOINS there might be NULL's coming back from the JOIN's
How does this work: COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result
IIF is a shortcut for "If Then Else"
If M2.CountS>0 Then 0 Else M3.EXTPRICE --> I agree with tg, that there might be NULL coming back for CountS, so i don't know how CountS>0 behaves (does it return True or False)
Let's Say, CountS=0 then the Else-Part comes into play --> M3. EXTPRICE
Now the COALESCE starts working.
COALESCE returns the FIRST NON-Null Value of its arguments
COALESCE evaluates its first Argument, which is the IIF.
We said, the IIF returns the M3.EXTPRICE (because CountS was 0).
Now if M3 returns a valid EXTPRICE all is well (because in ITEM there was at least one "EQ_PORT")
But M3 returns NULL for EXTPRICE if there is no "EQ_PORT" in ITEM
In that case COALESCE jumps to its next Argument, which is M4.EXTPRICE, which it returns provided M4.EXTPRICE is NON-Null. That's the reason for the "Ooops" in the third argument
Last edited by Zvoni; Dec 1st, 2022 at 07:23 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 1st, 2022, 12:46 PM
#20
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
I'm still looking at this myself to see if I can figure out what it wants, but these are my errors when I run that query:
Msg 8120, Level 16, State 1, Line 2
Column 'M2.CountS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'M3.EXTPRICE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'M2.CountS' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'M3.EXTPRICE' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'M4.SumPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 8120, Level 16, State 1, Line 2
Column 'M4.SumPrice' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
EXTPRICE and ITEM are in the P21 view, since you asked. I made that change.
Code:
-- zvoni's way:
SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result
FROM Jobs AS M1
LEFT JOIN
/*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
(SELECT P21.SONO AS SONumber, COUNT(P21.disposition) AS CountS
FROM xqryP21LinesRelated AS P21
WHERE P21.disposition='S'
GROUP BY P21.SONO) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN
(SELECT J2.SONumber, P21.EXTPRICE
FROM Jobs AS J2
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J2.SONumber
WHERE P21.ITEM='EQ_PORT') AS M3
ON M3.SONumber=M1.SONumber
LEFT JOIN
(SELECT J3.SONumber, SUM(P21.EXTPRICE) AS SumPrice
FROM Jobs AS J3
INNER JOIN xqryP21LinesRelated AS P21 ON P21.SONO = J3.SONumber
--WHERE P21.disposition='A'
WHERE P21.disposition IS NULL
GROUP BY J3.SONumber) AS M4
ON M4.SONumber=M1.SONumber
GROUP BY M1.SONumber
This is a big pain in the ass and I am still trying to get answers from my management but FYI I may be doing this in C# code after all. My boss said if she can click a button to populate a grid and export it to Excel she's fine with that, so realize I have other alternatives. If I could just nail down the specs that would be so nice! And you've done so much work I feel you are invested and would feel bad if I copped out. But again, more specs may change our (your) work so far.
P.S. You mentioned you are in Germany. I took my daughters to Berlin and Ralswiek in May, traveling from the USA. Kristina loves your music over there and went to Mono Inc and Santiano concerts, in Berlin and Ralswiek respectively. This trip has been on her wishlist for a few years; glad I could give it to her :-).
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 1st, 2022, 01:02 PM
#21
Re: SQL Server Query - count if any rows have a certain value
OK, my Mistake.
Remove the LAST GROUP BY
and EXTPRICE comes from P21, so here goes
Code:
SELECT M1.SONumber, COALESCE(IIF(M2.CountS>0,0,M3.EXTPRICE), M4.SumPrice, 'Ooops') AS Result
FROM Jobs AS M1
LEFT JOIN
/*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
(SELECT P21.SONO AS SONumber, COUNT(P21.disposition) AS CountS
FROM xqryP21LinesRelated AS P21
WHERE P21.disposition='S'
GROUP BY P21.SONO) AS M2
ON M1.SONumber=M2.SONumber
LEFT JOIN
/*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
(SELECT P21.SONO AS SONumber, P21.EXTPRICE
FROM xqryP21LinesRelated AS P21
WHERE P21.ITEM='EQ_PORT') AS M3
ON M3.SONumber=M1.SONumber
LEFT JOIN
/*Optimized: There is no need to JOIN Jobs with P21, since we can get all Fields from P21*/
(SELECT P21.SONO AS SONumber, SUM(P21.EXTPRICE) AS SumPrice
FROM xqryP21LinesRelated AS P21
--WHERE P21.disposition='A'
WHERE P21.disposition IS NULL
GROUP BY P21.SONumber) AS M4
ON M4.SONumber=M1.SONumber
/*WHERE M1.SONumber=1066152*/
The Aggregation (and the need for a GROUP BY) happens in the inner Selects within the LEFT JOINS
So SQL-Server run afoul of the last group by
I've removed the GROUP BY, and added a comment with a WHERE-Clause for you to check
P.S. You mentioned you are in Germany. I took my daughters to Berlin and Ralswiek in May, traveling from the USA. Kristina loves your music over there and went to Mono Inc and Santiano concerts, in Berlin and Ralswiek respectively. This trip has been on her wishlist for a few years; glad I could give it to her :-).
I'm in Southwestern Germany (close to Switzerland), and frankly: Santiano is really not my style.
I like it loud and heavy *headbang*
Last edited by Zvoni; Dec 1st, 2022 at 01:06 PM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 1st, 2022, 01:03 PM
#22
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
Also, since you have plunged into this so deeply I will share with you where I am headed. I was trying to start simple but you mentioned something about joining to a table I may not need. This is my original query, with the columns I ultimately need to select, that my boss said had something wrong with it:
Code:
select Jobs.Control, Jobs.SONumber, Jobs.JobType, xtblSaleType.Description as SaleType, Jobs.CustNo, Jobs.CustomerName,
[dbo].[fn_GetJobShopTask](Jobs.Control, null) as CST,
dlr.SaleAmount, dlr.SaleDate, convert(nvarchar(max), Jobs.Details),
sum(p21.EXTPRICE) as SumDispositionA
from Jobs
join xtblForecastDealer dlr on dlr.JobControl = Jobs.Control
join [xtblSaleType] on [xtblSaleType].Control = Jobs.SaleType
left join xqryP21LinesRelated p21 on p21.SONO = Jobs.SONumber
where JObs.OrderIncludesActivities != -1
and JObs.Status != 'CP'
and JObs.JobType != 'Sale - MR'
and p21.disposition is null
and LEFT(Jobs.SONumber, 1) != 'C'
group by Jobs.Control, JObs.SONumber, Jobs.JobType, xtblSaleType.Description, JObs.CustNo, Jobs.CustomerName,
[dbo].[fn_GetJobShopTask](Jobs.Control, null),
dlr.SaleAmount, dlr.SaleDate, convert(nvarchar(max), Jobs.Details)
order by jobs.Control
The problem was simply getting a sum of P21.EXTPRICE. There are conditions (the criteria you've been coding) that have to be considered before we can accumulate the EXTPRICE.
Again, tell me you've had it if you want. Or wait until I have more info on what *exactly* I need to select.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 1st, 2022, 01:07 PM
#23
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
OK, hold up. I am going to post something but it's long and will take me a few minutes to compose and I didn't want us cross posting. So wait until you see my next post.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 1st, 2022, 01:12 PM
#24
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
P.S. Just out of curiosity, name a few loud and heavy headbanging groups. I believe there has to be some overlap. I mean Rammstein is an obvious mention and Kristina saw them here on their stadium tour in Sept (we had seem them several years ago on Long Island New York), and her friend from Lithuania, who she met online in a Rammstein fan group, visited us for a week so they could go together. (Sandra had also seen them in a Baltic country a month or two before). I am half-Lithuanian so it was fun hosting her! (Mock is one syllable of my 3-syllable last name, LOL).
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 1st, 2022, 01:27 PM
#25
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
I am running the following query against one SONumber to take a closer look (SONumber = SONO = order_no).
Note, customer_part_number is what we've been calling ITEM.
Code:
SELECT customer_part_number, P21.dbo.p21_view_oe_line.extended_price, P21.dbo.p21_view_oe_line.disposition, P21.dbo.p21_view_oe_line.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line
WHERE P21.dbo.p21_view_oe_line.order_no = '1066152'
It returns the results in my snip below. My goal is add up the extended_price column according to these conditions to get total *allocated* inventory.
We always skip HOLD.
In this case, we skip EQ_PORT's price of $24,835 because there's a child record with a disposition S. (Any row that has a non zero parent-uid is a child).
We skip SHIP's price of $248 because its a non-inventory item (which I might just always be able to skip SHIP).
The other 3 are exlcuded because they are actual child records.
Therefore answer/sum is 0.
But see how complicated and specific this is, which is why I feel I could control it better if I did it in a sequential programming language? I could get this resultset (and all the others) and examine them more easily looping through each set. And that might just be me that finds that way easier but you might not be here next year when I have to enhance it! (I mean, I'm sure you'd still be here, but maybe too busy with your own work!)
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 1st, 2022, 02:48 PM
#26
Re: SQL Server Query - count if any rows have a certain value
Starting with this:
Code:
SELECT customer_part_number, P21.dbo.p21_view_oe_line.extended_price, P21.dbo.p21_view_oe_line.disposition, P21.dbo.p21_view_oe_line.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line
WHERE P21.dbo.p21_view_oe_line.order_no = '1066152'
That gets your initial results...
Step 0 - apply aliases to clean it up:
Code:
SELECT tblP21.customer_part_number, tblP21.extended_price, tblP21.disposition, tblP21.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line tblP21
WHERE tblP21.order_no = '1066152'
Now applying the rest of the requirements one by one:
* We always skip HOLD.
Code:
SELECT tblP21.customer_part_number, tblP21.extended_price, tblP21.disposition, tblP21.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line tblP21
WHERE
tblP21.customer_part_number <> 'HOLD'
and tblP21.order_no = '1066152'
* In this case, we skip EQ_PORT's price of $24,835 because there's a child record with a disposition S. (Any row that has a non zero parent-uid is a child).
Code:
SELECT tblP21.customer_part_number,
case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price,
p21_view_oe_line.disposition, p21_view_oe_line.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line tblP21
left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
WHERE
tblP21.customer_part_number <> 'HOLD'
and tblP21.p21_view_oe_line.order_no = '1066152'
* We skip SHIP's price of $248 because its a non-inventory item (which I might just always be able to skip SHIP).
Code:
SELECT tblP21.customer_part_number,
case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price,
p21_view_oe_line.disposition, p21_view_oe_line.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line tblP21
left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
WHERE
tblP21.customer_part_number <> 'HOLD'
and tblP21.customer_part_number <> 'SHIP'
and tblP21.p21_view_oe_line.order_no = '1066152'
* The other 3 are exlcuded because they are actual child records.
Code:
SELECT tblP21.customer_part_number,
case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price,
p21_view_oe_line.disposition, p21_view_oe_line.parent_oe_line_uid
FROM P21.dbo.p21_view_oe_line tblP21
left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
WHERE
tblP21.customer_part_number <> 'HOLD'
and tblP21.customer_part_number <> 'SHIP'
and tblP21.parent_oe_line_uid <> 0
and tblP21.p21_view_oe_line.order_no = '1066152'
* Therefore answer/sum is 0.
Code:
SELECT tblP21.order_no,
SUM(case when tblP21.customer_part_number = 'EQ_PORT' and coalesce(SRowsCount.rowCount, 0) <> 0 then then 0 else p21_view_oe_line.extended_price end as extended_price) as Total
FROM P21.dbo.p21_view_oe_line tblP21
left join (select id, sum(1) as rowCount from P21.dbo.p21_view_oe_line where disposition = 'S' and tblP21.order_no = order_no) SRowsCount
WHERE
tblP21.customer_part_number <> 'HOLD'
and tblP21.customer_part_number <> 'SHIP'
and tblP21.parent_oe_line_uid <> 0
and tblP21.p21_view_oe_line.order_no = '1066152'
GROUP BY tblP21.order_no
that should work... no guarnatees though.,...
-tg
-
Dec 1st, 2022, 04:20 PM
#27
Re: SQL Server Query - count if any rows have a certain value
You have two „then“ after the coalesce
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 2nd, 2022, 06:18 AM
#28
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by MMock
P.S. Just out of curiosity, name a few loud and heavy headbanging groups. I believe there has to be some overlap. I mean Rammstein is an obvious mention and Kristina saw them here on their stadium tour in Sept (we had seem them several years ago on Long Island New York), and her friend from Lithuania, who she met online in a Rammstein fan group, visited us for a week so they could go together. (Sandra had also seen them in a Baltic country a month or two before). I am half-Lithuanian so it was fun hosting her! (Mock is one syllable of my 3-syllable last name, LOL).
I've seen Rammstein in the early "00" in what you would consider "small" venues (capacity some 5-6K people)
As for german "loud and heavy", the probably two "known" names would be
Scorpions and Accept
who are both pretty much "old socks".
And there is nothing new on the horizon... :-(
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Dec 2nd, 2022, 08:10 AM
#29
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
I wrote the C# code so I will compare proffered solutions to my output... Stay tuned.
Scorpions - I did not know they are German!
Accept - I have to ask my daughter.
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 2nd, 2022, 08:34 AM
#30
Re: SQL Server Query - count if any rows have a certain value
Accept are 100% German (I mean: Udo Dirkschneider ??!! )
Also you are forgetting Helloween (pumpkins, booo!) and Running Wild (pirates, aaarggg!).
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Dec 2nd, 2022, 09:24 AM
#31
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
So just for the fun of it, I am going to post my C# code for comparison, and I also have something to say about the maintenance I mentioned before.
I am pulling a dataset from sql and adding onto it the new column that I need to have calculated.
gcOpenOrderAllocate is my gridcontrol.
Here's that:
Code:
private void LoadGrid()
{
// The Fill will run this query:
// select Jobs.Control, SONumber, JobType, xtblSaleType.Description as SaleType, CustNo, CustomerName,
// [dbo].[fn_GetJobShopTask](Jobs.Control, null) as CST,
// dlr.SaleAmount, dlr.SaleDate, Details
// from Jobs
// join xtblForecastDealer dlr on dlr.JobControl = Jobs.Control
// join[xtblSaleType] on[xtblSaleType].Control = Jobs.SaleType
// where OrderIncludesActivities != -1
// and Status != 'CP'
// and JobType != 'Sale - MR'
// order by SONumber
// Each row wants to go in the grid. To this dataset we want to add a column for Allocated. So we have to take each row's SONumber and run another query to get results to calculate that amound.
using (dsOpenOrderAllocatedTableAdapters.DataTable1TableAdapter ta = new dsOpenOrderAllocatedTableAdapters.DataTable1TableAdapter())
{
using (dsOpenOrderAllocated.DataTable1DataTable dt = new dsOpenOrderAllocated.DataTable1DataTable())
{
ta.Fill(dt);
dt.Columns.Add(new DataColumn("AllocatedAmount",System.Type.GetType("System.Decimal")));
foreach (dsOpenOrderAllocated.DataTable1Row dr in dt.Rows)
{
dr["AllocatedAmount"] = CalculateAllocated(dr.SONumber);
}
gcOpenOrderAllocate.DataSource = dt;
}
}
}
Here is CalculateAllocated() with the logic we've come to know and love:
Code:
private decimal CalculateAllocated(string SONumber)
{
decimal totalAmount = 0;
string sFilter;
//dsOpenOrderAllocated.DataTable2DataTable dtChildLinesDispositionA;
DataTable dtChildLinesDispositionA;
using (dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter ta = new dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter())
{
using (dsOpenOrderAllocated.DataTable2DataTable dt = new dsOpenOrderAllocated.DataTable2DataTable())
{
ta.Fill(dt, SONumber);
foreach (dsOpenOrderAllocated.DataTable2Row dr in dt.Rows)
{
// We are going to get a set of rows back and decided if we want to accumulate their extended_price.
// Sometimes this will depends on what's in the other rows or what's in the row's other columns.
// It's complicated
// We don't calculate HOLD's
if (dr.customer_part_number == "HOLD")
continue;
if (dr.class_id4 == null)
{
totalAmount += dr.extended_price;
}
else
{
switch (dr.class_id4)
{
case "EQ":
// Accumulate EQ's extended price unless it's a child line
if (dr.parent_oe_line_uid == 0)
totalAmount += dr.extended_price;
break;
case "ITEM":
// Accumulate the ITEM's extended price
totalAmount += dr.extended_price;
break;
case "LABOR":
case "OC":
case "OPTION":
case "PROJ":
case "SA":
case "WARR":
break;
case "PACKAGE":
// Confirm there's a child and its disposition is A, if so accumulate the PACKAGE's extended price.
sFilter = "parent_oe_line_uid > 0 AND disposition = 'A'";
using (DataView view = new DataView(dt, sFilter, "", DataViewRowState.CurrentRows))
{
dtChildLinesDispositionA = view.ToTable();
if (dtChildLinesDispositionA.Rows.Count > 0)
totalAmount += dr.extended_price;
}
break;
}
}
}
}
}
return totalAmount;
}
By the way, disposition = null is the same as dispostion A, I can't recall what I've said about that, whether you know NULL is "converted" to A. But they are one and the same for our purposes.
So given that code, we already found a logic error. The VP just pointed out to me that we want to not accumulate any disposition = 'S'. That is very easy for me to add into the top of the foreach loop. I haven't worked with the query yet, but I am afraid it would not be so very easy to add it there. I know I asked a question in my OP, about how to write a query and you are helping me with that, but as the specs evolved and my boss said ultimately she just needs an Excel file (which is a simple button-click operation in my code as I export grids to Excel all the time and have the code), that is why I'm saying maybe C# code is a better way.
Thoughts?
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 2nd, 2022, 09:31 AM
#32
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
Originally Posted by sapator
Also you are forgetting ... Running Wild (pirates, aaarggg!).
Pirates are why I believe she likes Santiano, sea shanty music!
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
-
Dec 2nd, 2022, 12:43 PM
#33
Thread Starter
PowerPoster
Re: SQL Server Query - count if any rows have a certain value
And you'll never guess what just happened? Now they want an unallocated bucket in addition to an allocated bucket. So my local accumulating value totalAmount just got tossed and I have global variables totalAllocatedAmount and totalUnallocatedAmount. And CalculateAllocated() (which is now a misnomer!), goes like this:
Code:
/// <summary>
/// CalculateAllocated() - 12/01/22 Written.
/// 12/02/22 - Originally it returned the allocated amount. Dawn just asked me if we could also have the unallocated amount. So a couple changes to do that. I will make its return type void
/// and the two amounts will be global variables.
/// </summary>
/// <param name="SONumber"></param>
/// <returns></returns>
private void CalculateAllocated(string SONumber)
{
//decimal totalAmount = 0;
totalAllocatedAmount = 0;
totalUnallocatedAmount = 0;
string sFilter;
//dsOpenOrderAllocated.DataTable2DataTable dtChildLinesDispositionA;
DataTable dtChildLinesDispositionA;
using (dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter ta = new dsOpenOrderAllocatedTableAdapters.DataTable2TableAdapter())
{
using (dsOpenOrderAllocated.DataTable2DataTable dt = new dsOpenOrderAllocated.DataTable2DataTable())
{
ta.Fill(dt, SONumber);
foreach (dsOpenOrderAllocated.DataTable2Row dr in dt.Rows)
{
// We are going to get a set of rows back and decide if we want to accumulate their extended_price as allocated or unallocated.
// Sometimes this will depend on what's in the other rows or what's in the row's other columns.
// It's complicated
// We don't accumulate HOLD's. Ever.
if (dr.customer_part_number == "HOLD")
{
continue;
}
// 12/02/22 - New requirement because of the SONumber Dawn and I just went through, 1067098. It has like 45 rows so it's a good test.
if (dr.disposition == "S" || dr.disposition == "B" || dr.disposition == "H")
{
totalUnallocatedAmount += dr.extended_price;
continue;
}
if (dr.class_id4 == null)
{
totalAllocatedAmount += dr.extended_price;
}
else
{
switch (dr.class_id4)
{
case "EQ":
// Accumulate EQ's extended price unless it's a child line
if (dr.parent_oe_line_uid == 0)
totalAllocatedAmount += dr.extended_price;
else
{
if (dr.disposition == "A")
;
else
totalUnallocatedAmount += dr.extended_price;
}
break;
case "ITEM":
// Accumulate the ITEM's extended price
totalAllocatedAmount += dr.extended_price;
break;
case "LABOR":
case "OC":
case "OPTION":
case "PROJ":
case "SA":
case "WARR":
break;
case "PACKAGE":
// Confirm there's a child and its disposition is A, if so accumulate the PACKAGE's extended price.
sFilter = "parent_oe_line_uid > 0 AND disposition = 'A'";
using (DataView view = new DataView(dt, sFilter, "", DataViewRowState.CurrentRows))
{
dtChildLinesDispositionA = view.ToTable();
if (dtChildLinesDispositionA.Rows.Count > 0)
totalAllocatedAmount += dr.extended_price;
else
totalUnallocatedAmount += dr.extended_price;
}
break;
}
}
}
}
}
//return totalAmount;
}
This is why I usually have a glass of wine when I go home at the end of the workday...
There are 10 kinds of people in this world. Those who understand binary, and those who don't.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|