CODEWITHSUNDEEP
sqlsum
bsandrabr
bsandrabr

Reputation: 143

making a combined sum of two columns

I have a table (apples) containing:

cid  date_am date_pm 
----------------------
1      1       1
2      2       1
3      1       3  
1      1       2

I asked a question earlier (badly) about how I would rank the customers in order of the number of ones(1) they had. The solution was (based on one column):

SELECT cid, sum( date_pm ) AS No_of_ones
FROM apples
WHERE date_am =1
GROUP BY cid
ORDER BY no_of_ones DESC

This works great for one column but how would I do the same for the sum of the two columns. ie. 

SELECT cid, sum( date_pm ) AS No_of_ones
FROM apples
WHERE date_am =1
add to
SELECT cid, sum( date_am ) AS No_of_ones
FROM apples
WHERE date_pm =1
GROUP by cid
ORDER by no_of_ones(added)

hope I've managed to make that clear enough for you to help -thanks

Upvotes: 2

Views: 1255

Answers (2)

ChickenMilkBomb
ChickenMilkBomb

Reputation: 945

select cid, sum(addone) as total from 
  (select cid, 1 as addone
   from apples
    where date_pm = 1 group by cid
    union
    select cid, 1 as addone
    from apples
    where date_am = 1 group by cid) 
group by cid order by total DESC

OR

select cid, sum(case when date_am=1 then 1 else 0 end) 
            + sum(case when date_pm=1 then 1 else 0 end) as total 
from apples 
group by CID 
order by total DESC

Upvotes: 0

Konrad Garus
Konrad Garus

Reputation: 54045

select cid, sum(case when date_pm = 1 then 1 else 0 end) + sum(case when date_am = 1 then 1 else 0 end)
from apples
group by cid

Upvotes: 1

Related Questions