Hi guys!
I have the following MSSQL query:
SELECT ModuleID, SUM(CASE ST80_Image_3_Complete When 0 THEN 1 else NULL end) AS CapCrimp
FROM ST080_Pallet_info
Where ST80_Rejected=-1
Group by moduleID
order by moduleID
This is an error counter (for a specific error) sorted by part number. I get a result set like this:
So here’s the question. Can I get this to exclude rows that have a sum of null?
Jordan,
You will need to add a T-SQL HAVING clause to your query. Try this…
SELECT ModuleID, SUM(CASE ST80_Image_3_Complete When 0 THEN 1 else NULL end) AS CapCrimp
FROM ST080_Pallet_info
Where ST80_Rejected=-1
Group by moduleID
HAVING SUM(CASE ST80_Image_3_Complete When 0 THEN 1 else NULL end) > 0
order by moduleID
Yep! That was the vital piece I was missing!
I’ve used HAVING in other aggregates, so I have no idea why I didn’t think of it before.
I’m sure it’s the flu… yeah, that’s it…
Jordan,
Been there, done that!
Technically, this version is more correct:
SELECT ModuleID, SUM(CASE ST80_Image_3_Complete When 0 THEN 1 else NULL end) AS CapCrimp
FROM ST080_Pallet_info
Where ST80_Rejected=-1
Group by moduleID
HAVING SUM(CASE ST80_Image_3_Complete When 0 THEN 1 else NULL end) IS NOT NULL
order by moduleID
I wasn’t sure of the syntax because I didn’t have access to a server at the time but I’ve since verified that the ‘IS NOT NULL’ works too.