mysql - 使用sum，指望mysql中的同一列

我有一个表，其中有动作和消息作为列，我想在特定日期计算特定动作，我可以对每个动作求和，但不计算特定动作。

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））作为评论。我不能这样做。

最佳答案

要获得操作2、3和4的总和（作为列审查）和操作23、24、25（作为违反），您可以执行以下操作：

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

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

关于mysql - 使用sum，指望mysql中的同一列，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/20518915/