someman someman - 1 year ago 59
SQL Question

count * twice with the same column under different conditions

I am trying to count(*) twice the same column under different where conditions of the same column

Here is what I have; I tried many things but could not get it to work. I don't want to write two different queries.

SELECT somedate as date,
count(*) as goodone,
sum(money) as goodcost
FROM goods
where goodstatus=2 group by date;

when goodstatus = 1 that is the badone, so I also want to include that, and not write a separate query for it.

At the end, what I want is

somedate | goodone | badones| goodcost

I get a subquery returned more than one row error if I try doing a

(SELECT count(*) FROM goods where goodstatus=1) as badones

in that query above.

Answer Source

Count the records based on condition instead of adding a new query

Try this way

SELECT somedate            AS date,
       Sum(goodstatus = 2) AS goodone,
       Sum(goodstatus = 1) AS badones,
       Sum(money)          AS goodcost
FROM   goods
WHERE  goodstatus IN ( 1, 2 )
GROUP  BY date; 

Another way using CASE statement

count(case when goodstatus = 2 then 1 end) AS goodone,
count(case when goodstatus = 1 then 1 end) AS badones,

In the above CASE statement, 1 will be resulted when the condition is satisfied else NULL is resulted. Count aggregate will not count NULL values through this we can find the number of occurrence of goodstatus = 2 or goodstatus = 1

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download