using sum,count on the same column in mysql

Question

I have a table where i have actions and messages as columns i want to count of particular actions on particular datetime i was able to sum on every action but not count on particular action.

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'

But this is what I need adding it count(AcceptedIMG, RejectedIMG,ChangeIMG,) or (count(action(2,3,4) as review. I am not able to do this.

That SUM is doing the COUNT. You need to GROUP BY DATE(datetime),carpark_name to get your results correctly. — Filipe Silva
– Filipe Silva, Commented Dec 11, 2013 at 12:18
I mean to add these actions of(2,3,4) as review.(eg.like action(2+3+4 =XX) as review — preethi
– preethi, Commented Dec 11, 2013 at 12:22
Not understanding what you are trying to do. Could you add the expected output and more detail of what you are looking for? — Filipe Silva
– Filipe Silva, Commented Dec 11, 2013 at 12:25
i need sum of action (2,3,4) as review and sum(23,24,25) as contravention. LIKE ............................................ ..................datetime carpark, accepted , rej,change, review .................2013-12-03 ebbwale 5 4 3 12 — preethi
– preethi, Commented Dec 11, 2013 at 12:26

Filipe Silva · Accepted Answer · 2013-12-11 12:31:03Z

To get the SUM of action 2,3 and 4, as column REVIEW and 23,24,25 as CONTRAVENTIO you could do:

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

I just added the two columns to your query. If you don't need the individual ones, you can remove them.

Collectives™ on Stack Overflow

using sum,count on the same column in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related