SQL DB2: How to count multiple conditions on the same line?

[VGlushkin]

Just recently I discovered that it is possible to output COUNT(*) results in several columns, if use a conditional sum technique.

Imagine that you need to output 3 columns: Branch Plant, Number of accounts with bill flag “Y” and number of accounts with bill flag “N”.  It means you need to count two different conditions on the same output line.  Normal COUNT(*) mechanism doesn’t allow to do that. But it is still possible.

SELECT GMMCU, sum(case when gmbill = 'Y' then 1 else 0 end) AS
COUNT_Y, sum(case when gmbill <>'Y' then 1 else 0 end) AS
COUNT_OTHER FROM F0901 WHERE gmobj = '14523' GROUP BY GMMCU HAVING
sum(case when gmbill = 'Y' then 1 else 0 end)> 0 ORDER BY GMMCU

The 3-column output for this expression would look like this:

=========================================
Business        COUNT_Y       COUNT_OTHER
Unit
=========================================
522037            1                0
523260            4                0
523262            6                0
523263            4                0
523264            7                3
=========================================

Let’s see that this SQL statement actually does?
Instead of COUNT(*), we are using here SUM() with condition iside:
sum(case when gmbill = 'Y' then 1 else 0 end)

That way we can group by one field, and output all kind of summarized information about records of that type in next columns. Perfect. Enjoy!

[Author]

Posts