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:
![](https://global.discourse-cdn.com/business4/uploads/inductiveautomation/original/1X/5b70691f803e20f9976b785d9d522d4133ad3e54.jpg)
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. ![:blush: :blush:](http://forum.inductiveautomation.com/images/emoji/twitter/blush.png?v=6)
I’m sure it’s the flu… yeah, that’s it… ![:laughing: :laughing:](http://forum.inductiveautomation.com/images/emoji/twitter/laughing.png?v=6)
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.