-
Sep 25th, 2020, 09:05 AM
#1
Thread Starter
Fanatic Member
[RESOLVED] SQL - Query to return sum and 0 if no match is found
Hi
I have a table (HOC) containing the list of some items. I need to check another table (DA ) against HOC and sum up the occurrences of items and if no item is found then return 0 for sum.
My query:
Code:
Select HOC.ListItemName,
Sum(Case When DA.Hierarchy IS not null then 1 else 0 End) As Total
--COALESCE(Sum(Case When DA.Hierarchy IS not null then 1 else 0 End) , 0) As Total
From DV_ListItems as HOC
Left join DV_CaseOverview AS DA
On HOC.ListItemName = Cast(DA.HierarchyOrder as varchar(2)) + ': ' + DA.Hierarchy
Join [DV_Data_NEW] As NG
ON NG.DEVIATION_NUMBER = DA.CaseID
Where
HOC.ListItemType = 'DV Hierarchy'
And ( Format(NG.[Completed_DATE],'yyyy-MM-dd') >= '2020-06-25' And Format(NG.[Completed_DATE],'yyyy-MM-dd') <= '2020-09-25')
Group by HOC.ListItemName
This query does not show all the relevant items that exist in HOC; and give me the sum of only occurrences:
How can I fix this? is this because of how I join them? Is it because of
Code:
On HOC.ListItemName = Cast(DA.HierarchyOrder as varchar(2)) + ': ' + DA.Hierarchy
and so if not both table contain the same item, then it won't show up in the result? but left join should be the correct method right?
Thanks in advance for your help.
-
Sep 25th, 2020, 10:28 AM
#2
Re: SQL - Query to return sum and 0 if no match is found
IT would have been... but then you inner joined here:
Code:
Join [DV_Data_NEW] As NG
ON NG.DEVIATION_NUMBER = DA.CaseID
If there is no row in DA, then joining to NG negates the left join... your join to NG also needs to be left join
AND
you'll need to account for that here:
Code:
And ( Format(NG.[Completed_DATE],'yyyy-MM-dd') >= '2020-06-25' And Format(NG.[Completed_DATE],'yyyy-MM-dd') <= '2020-09-25')
Since those date will not exist...
-tg
-
Sep 25th, 2020, 02:05 PM
#3
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
Thank you.
How should I account for those dates; as soon as I use them then the result is messed up.
-
Sep 25th, 2020, 02:47 PM
#4
Re: SQL - Query to return sum and 0 if no match is found
Well for starters, stop formatting them... formatting implies you care about what they look, which you shouldn't... they're dates... as you have it now, you're treating them like strings. Secondly if they don't exist, they would be null, so check for their null-ness.
-tg
-
Sep 25th, 2020, 02:56 PM
#5
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
I got you about the format.
But, then those dates jsut set the period for the data range I am interested in. It is all good for the rest of the items. Waht I want to see is that there is no "lagging Control" or rather a row containing: "1: Lagging Control = 0" and the rrest of the stuff. I am sure that there is no cases inthat period that is marked with "lagging Control" and so I expected to see a row with zero as sum.
Last edited by Grand; Sep 25th, 2020 at 03:21 PM.
-
Sep 25th, 2020, 03:22 PM
#6
Re: SQL - Query to return sum and 0 if no match is found
Like I said... you need to test for the null-ness of the dates:
Code:
Select HOC.ListItemName,
Sum(Case When DA.Hierarchy IS not null then 1 else 0 End) As Total
From DV_ListItems as HOC
Left join DV_CaseOverview AS DA
On HOC.ListItemName = Cast(DA.HierarchyOrder as varchar(2)) + ': ' + DA.Hierarchy
left Join [DV_Data_NEW] As NG
ON NG.DEVIATION_NUMBER = DA.CaseID
Where
HOC.ListItemType = 'DV Hierarchy'
And (
(NG.[Completed_DATE] between '2020-06-25' And '2020-09-25')
or
(NG.[Completed_DATE] is null)
)
Group by HOC.ListItemName
I'm assuming you're using SQL Server...
-tg
-
Sep 25th, 2020, 03:32 PM
#7
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
Thank you so much for your tireless effort here.
I am however getting the same result
just a side question, why those dates are important here adn more specifically why they should be null? isn't the HOC.ListItemName I should focus on?
-
Sep 25th, 2020, 03:45 PM
#8
Re: SQL - Query to return sum and 0 if no match is found
Follow:
You have the following:
Table A
1 -- Item 1
2 -- Item 2
3 -- Item 3
Table B
2 -- Item 2
3 -- Item 3
Table C
2 -- Item 2
3 -- Item 3
You're doing this:
select a.items, sum(case when b.items is null then 0 else 1 end) cnt
from tablea a
left join tableb b on a.id = b.id
left join tablec c on b.id = c.id
where c.items = "Item 3" or c.item is null
group by a.items
It's not 100% accurate but it's close to illustrate hte point...
that should return Item 1 & Item 3 ... with counts 0 & 1 respectively...
You're starting with a complete list... then moving to a partial list... left join is appropriate... it keeps the full list... in your original code, you then switch to an inner join... well, that then drops anything missing... if you keep it as a left join, it will keep missing rows... but then you have dates in your where clause... but the dates come from your thrid table... which means they could be missing (NULL) ... it just so happens, those are the rows you DO want... so you need to includde them somehow...
-tg
-
Sep 25th, 2020, 03:47 PM
#9
Re: SQL - Query to return sum and 0 if no match is found
If it's still not working, then you may want to post some sample SQL that will create the tables and sample data in the tbles that we can use to replicate hte problem... w/o knowing what your data looks like or how it is in the tab le,s it's getting harder to see what the problem is.
-tg
-
Sep 28th, 2020, 04:14 AM
#10
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
Thank you for taking time to look into this.
When I try to upload an Excel file hereĀ“, it tells me invalid file thing. Which format shall I upload the tables?
-
Sep 28th, 2020, 07:14 AM
#11
Re: SQL - Query to return sum and 0 if no match is found
As I said:
some sample SQL that will create the tables and sample data in the tables that we can use to replicate the problem.
-tg
-
Sep 28th, 2020, 07:31 AM
#12
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
Thanks again.
Edit, now with SQL
Last edited by Grand; Sep 30th, 2020 at 02:12 AM.
-
Sep 29th, 2020, 03:57 AM
#13
Re: SQL - Query to return sum and 0 if no match is found
Originally Posted by Grand
Thank you for taking time to look into this.
When I try to upload an Excel file hereĀ“, it tells me invalid file thing. Which format shall I upload the tables?
zip it first!
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
-
Sep 29th, 2020, 04:07 AM
#14
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
Thanks. Is the SQL code for creating temporary sample sufficient?
-
Sep 29th, 2020, 04:19 AM
#15
Re: SQL - Query to return sum and 0 if no match is found
On SQLite (don't have SQLServer)
You have to test DA.CaseID if it's null
Code:
SELECT
HOC.ListItemName,
SUM(CASE WHEN C.CaseID ISNULL THEN 0 ELSE 1 END) As TotalSum
FROM HOC
LEFT JOIN
(SELECT
DA.CaseID,
DA.HierarchyOrder||': '||DA.Hierarchy AS DAItem,
NG.DEVIATION_NUMBER,
NG.COMPLETED_DATE
FROM DA
INNER JOIN NG
ON
DA.CaseID=NG.DEVIATION_NUMBER) AS C
ON
HOC.ListItemName=C.DAItem
GROUP BY
HOC.ListItemName
ORDER BY
HOC.ListItemName
Returns
Code:
0: No Control |
1 |
1: Lagging Control |
0 |
2: Sign/ Information/ Training |
1 |
3: 4P Training |
2 |
4: Procedure/ Standard |
0 |
5: Leading Control |
0 |
6: Poka Yoke |
0 |
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
-
Sep 29th, 2020, 05:07 AM
#16
Thread Starter
Fanatic Member
Re: SQL - Query to return sum and 0 if no match is found
Thanks a lot
Did a bit of modification and it does what I needed:
Code:
SELECT
HOC.ListItemName,
SUM(CASE WHEN C.CaseID IS NULL THEN 0 ELSE 1 END) As TotalSum
FROM DV_ListItems as HOC
LEFT JOIN
(SELECT
DA.CaseID,
Cast(DA.HierarchyOrder as varchar(2)) +': ' + DA.Hierarchy AS DAItem,
NG.DEVIATION_NUMBER,
NG.COMPLETED_DATE
FROM DV_CaseOverview AS DA
INNER JOIN DV_Data_NEW As NG
ON
DA.CaseID=NG.DEVIATION_NUMBER Where
( NG.[Completed_DATE] >= '2020-06-25' And NG.[Completed_DATE] <= '2020-09-29') ) AS C
ON
HOC.ListItemName=C.DAItem
Where HOC.ListItemType = 'DV Hierarchy'
GROUP BY
HOC.ListItemName
ORDER BY
HOC.ListItemName
Much appreciated.
-
Oct 2nd, 2020, 06:27 AM
#17
Thread Starter
Fanatic Member
Re: [RESOLVED] SQL - Query to return sum and 0 if no match is found
Hi again
I have spent now a few days to tune it to the full purpose but am unable to get it to work. The last bit is this: before this I had a little code that helped me to get an overview of the data that was missing values:
Code:
ISNULL(DA.Hierarchy,'Missing data') As Hierarchy
in this:
Code:
Select
Distinct ISNULL(DA.Hierarchy,'Missing data') As Hierarchy,
DA.HierarchyOrder ,
Sum(Case When DA.Hierarchy IS not null then 1 else 0 End) As Total
FROM [DV_Data_NEW] As NG
Inner Join DV_CaseOverview AS DA
ON NG.DEVIATION_NUMBER = DA.CaseID
Where ( NG.[Completed_DATE]>= '2020-01-01' And NG.[Completed_DATE] <= '2020-10-02') And
NG.Staus = 'Closed' And
Group by DA.Hierarchy,
DA.HierarchyOrder
Order by Total DESC
that gave me this part of the data:
With the new approach, I am not sure how to get that as a part of the output
Any help is appreciated.
Thanks.
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
|