How can I sum two or more COUNT values in MySQL query?

Question

I want to sump up two or more counted values [pivoting] My query is as following:

SELECT 
     client_name.client_name , 
     escort ,
     count(case mission_status_reason when '4' then mission_status_reason end) as awaiting_upload_at_origin,
     count(case mission_status_reason when '6' then mission_status_reason end) as awaiting_military_escort,
     count(case mission_status_reason when '3' then mission_status_reason end) as enrouted_to_destination,
     count(case mission_status_reason when '9' then mission_status_reason end) as awaiting_download,
     ( awaiting_military_escort +enrouted_to_destination+ awaiting_download ) as TOTAL 
FROM usc_tmr 
     LEFT JOIN client_name on client_name.id = usc_tmr.client_name 
WHERE escort='usg' 
  and mission_status_ops IN ('1','4','5','6') 
GROUP BY client_name

My problem is calculating the TOTAL. Can you guys please help.

Have you tried using SUM()?

Joakim Johansson
– Joakim Johansson

2012-05-26 08:42:39 +00:00
Commented May 26, 2012 at 8:42 — Joakim Johansson
– Joakim Johansson, Commented May 26, 2012 at 8:42
All I get is Unknown column error

marifrahman
– marifrahman

2012-05-26 08:50:44 +00:00
Commented May 26, 2012 at 8:50 — marifrahman
– marifrahman, Commented May 26, 2012 at 8:50

eggyal · Accepted Answer · 2012-05-26 08:46:10Z

1

SELECT   client_name.client_name,
         escort,
         SUM(mission_status_reason = 4)        AS awaiting_upload_at_origin,
         SUM(mission_status_reason = 6)        AS awaiting_military_escort,
         SUM(mission_status_reason = 3)        AS enrouted_to_destination,
         SUM(mission_status_reason = 9)        AS awaiting_download,
         SUM(mission_status_reason IN (3,6,9)) AS TOTAL
FROM     usc_tmr LEFT JOIN client_name ON client_name.id = usc_tmr.client_name
WHERE    escort = 'usg' AND mission_status_ops IN (1,4,5,6)
GROUP BY client_name

answered May 26, 2012 at 8:46

eggyal

127k18 gold badges220 silver badges244 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I sum two or more COUNT values in MySQL query?

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