使得两列

Question

我有一个表（苹果）包含的组合总和：

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

我问一个问题早（严重）有关我将如何排名的客户的人的数量的顺序（1 ） 他们有。解决的办法是（基于一列）：

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

此为一列的伟大工程，但我会怎么做了两列的总和相同。 即。

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) 

希望我已经设法让足够清楚你帮-Thanks

Answer 1

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 

Answer 2

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