Moinkhan Moinkhan - 3 months ago 16
MySQL Question

Count of more then one different values in a single group when using GROUP BY clause of SQL

Consider i have following data in My table.
TableName : Task

+---------+
| status |
+---------+
| open |
| reopen |
| reopen |
| close |
| suspend |
| close |
+---------+


Now, i want to count record group by it's status.
But there is one change that, I want to count open and reopen like they are in same group.

So i execute this below query.

SELECT
status,COUNT(*)
FROM
Task
GROUP BY status


A) What I got.

B) What I want.

A) B)
+---------+---------+ +---------+---------+
| status | count | | status | count |
+---------+---------+ +---------+---------+
| open | 1 | | open | 3 |
| reopen | 2 | | close | 2 |
| close | 2 | | suspend | 1 |
| suspend | 1 | +---------+---------+
+---------+---------+


As you can see from above in A) I got the result of the query. But I want to count record of reopen in open it self.
That you can see in B).

And one more thing I don't want this count in horizonatly order.
Which i have tried. Below Query will work.

C)
SELECT
COUNT(case status when 'open' then 1 when 'reopen' then 1 else null end) as open
COUNT(case status when 'close' then 1 else null end) as close,
COUNT(case status when 'suspend' then 1 else null end) as suspend
FROM
Task
+---------+---------+---------+
| open | close | suspend |
+---------+---------+---------+
| 3 | 2 | 1 |
+---------+---------+---------+


So My quesion is How can I achive B) as I mentioned above without using option C) ?

You can try this sqlfiddle
http://sqlfiddle.com/#!9/fdf820

Answer

Option C is just overkill. All you need to do is convert "reopen" to "open"

SELECT   CASE status WHEN 'reopen' THEN 'open' ELSE status END, COUNT(*)
FROM     task
GROUP BY CASE status WHEN 'reopen' THEN 'open' ELSE status END