CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Ronald McDonald
Ronald McDonald

Reputation: 2911

SQL Do not return column if value is zero

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

I'm using this to populate the mschart control and I don't wan't labels on the chart if there are 0 instances of that 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

Upvotes: 4

Views: 7007

Answers (2)

DForck42
DForck42

Reputation: 20387

What I would do is take what you have, throw it into an unpivot, then remove all of the 0 records.

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

That does, of course, entail changing your chart some.

Upvotes: 1

Yuck
Yuck

Reputation: 50865

No, because the shape of the query (the fields it contains) has to be known. Only the data can change, and that is what you should be looking for. You can dynamically remove or hide labels based on 0 or null data in a column.

Upvotes: 5

Related Questions