Count of more then one different values in a single group when using GROUP BY clause of SQL
<p>Consider i have following data in My table.</p>

<p>TableName : Task</p>

<pre><code>+---------+
| status  |
+---------+
| open    |
| reopen  |
| reopen  |
| close   |
| suspend |
| close   |
+---------+
</code></pre>

<p>Now, I want to count record group by its status.
But there is one change that, I want to count <strong>open</strong> and <strong>reopen</strong> like they are in same group. </p>

<p>So I execute this below query.</p>

<pre><code>SELECT 
 status,COUNT(*)
FROM 
 Task
GROUP BY status
</code></pre>

<p>A) What I got.</p>

<p>B) What I want.</p>

<pre><code>A)                      B)
+---------+---------+   +---------+---------+
| status  | count   |   | status  | count   |
+---------+---------+   +---------+---------+
| open    | 1       |   | open    | 3       |
| reopen  | 2       |   | close   | 2       |
| close   | 2       |   | suspend | 1       |
| suspend | 1       |   +---------+---------+
+---------+---------+   
</code></pre>

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

<p>And one more thing I don't want this count in horizontal order.
Which U have tried. Below Query will work.</p>

<pre><code>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       |
+---------+---------+---------+
</code></pre>

<p>So My question is How can I achieve B) as I mentioned above without using option C) ?</p>

<p>You can try this sqlfiddle
<a href="http://sqlfiddle.com/#!9/fdf820" rel="nofollow">http://sqlfiddle.com/#!9/fdf820</a></p>