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: