mysql - 在 mysql 的同一列上使用 sum,count

Question

我有一个表，其中有操作和消息作为列我想计算特定日期时间的特定操作我能够对每个操作求和但不计算特定操作。

SELECT DATE(datetime),carpark_name,
Sum(CASE action when '2' then 1 else 0 end) as AcceptedIMG,
Sum(CASE action when '3' then 1 else 0 end) as RejectedIMG, 
Sum(CASE action when '4' then 1 else 0 end) as ChangeIMG,  
sum(CASE action when '23' then 1 else 0 end) as AcceptedViolation,
sum(CASE action when '24' then 1 else 0 end) as RejectedViolation,
sum(CASE action when '25' then 1 else 0 end) as SkippedViolation,
FROM customer_1.audit_trail inner join customer_1.carparks on
customer_1.audit_trail.location_id =  customer_1.carparks.id
 where DATE(datetime)> '2013-12-01'and DATE(datetime)< '2013-12-03'  and location_id = '146'

但这就是我需要添加它 count(AcceptedIMG, RejectedIMG,ChangeIMG,) 或 (count(action(2,3,4) 作为审查。我无法做到这一点。

Answer 1

要获得操作 2、3 和 4 的总和，作为 REVIEW 列，23、24、25 作为 CONTRAVENTIO，您可以这样做:

SELECT DATE(datetime),
       carpark_name,
       Sum(CASE action when '2' then 1 else 0 end) as AcceptedIMG,
       Sum(CASE action when '3' then 1 else 0 end) as RejectedIMG, 
       Sum(CASE action when '4' then 1 else 0 end) as ChangeIMG,  
       SUM(CASE WHEN action IN ('2','3','4') then 1 else 0 end) as REVIEW
       sum(CASE action when '23' then 1 else 0 end) as AcceptedViolation,
       sum(CASE action when '24' then 1 else 0 end) as RejectedViolation,
       sum(CASE action when '25' then 1 else 0 end) as SkippedViolation,
       SUM(CASE WHEN action IN ('23','24','25') then 1 else 0 end) as CONTRAVENTION
FROM customer_1.audit_trail 
INNER join customer_1.carparks 
   ON customer_1.audit_trail.location_id =  customer_1.carparks.id
WHERE DATE(datetime) > '2013-12-01'
  AND DATE(datetime) < '2013-12-03'  
  AND location_id = '146'
GROUP BY DATE(datetime),carpark_name

我刚刚将这两列添加到您的查询中。如果您不需要个别的，您可以删除它们。