Geek Answers Handbook

SQL Do Not Return Column If Value Is Zero

This query returns one row with Ready, Processing, Complete, Failed, and Error columns with totals for each. Is there a way to rewrite this query so that columns with null value are not returned?

I use this to populate the mschart control, and I cannot find labels on the chart if there are 0 instances of this category.

SELECT SUM(CASE WHEN Status = 'R' THEN 1 ELSE 0 END) AS Ready, SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) AS Processing, SUM(CASE WHEN Status = 'C' THEN 1 ELSE 0 END) AS Complete, SUM(CASE WHEN Status = 'F' THEN 1 ELSE 0 END) AS Failed, SUM(CASE WHEN Status = 'E' THEN 1 ELSE 0 END) AS Error FROM MailDefinition
+4
source share
2 answers

What I would do is take what you have, throw it in univot, and then delete all entries from 0.

 select Type, Sum from ( SELECT SUM(CASE WHEN Status = 'R' THEN 1 ELSE 0 END) AS Ready, SUM(CASE WHEN Status = 'P' THEN 1 ELSE 0 END) AS Processing, SUM(CASE WHEN Status = 'C' THEN 1 ELSE 0 END) AS Complete, SUM(CASE WHEN Status = 'F' THEN 1 ELSE 0 END) AS Failed, SUM(CASE WHEN Status = 'E' THEN 1 ELSE 0 END) AS Error FROM MailDefinition ) a unpivot ( Sum for Type in ([Ready],[Processing],[Complete],[Failed],[Error]) ) u where Sum>0

This, of course, is due to a change in schedule.

+1
source

No, because the request form (the fields contained in it) must be known. Only data can change, and this is what you should look for. You can dynamically delete or hide labels based on 0 or null data in a column.

+5
source

More articles:


All Articles