Ignore if sum=null

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. :blush:

I’m sure it’s the flu… yeah, that’s it… :laughing:

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.