MySQL: Count rows based on fields name

Question

My MySql db table structure is:

id | status
1  |  Open
2  |  Open
3  |  Closed
4  |  Open
5  |  Closed

and i want to count total number of Open and Closed rows.

Status | Total
Open   |   3
Closed |   2

I used

SELECT count(distinct status) as Total FROM my_tbl

but it returns

Total
  2

Sudhir Bastakoti · Accepted Answer · 2012-02-02 08:07:06Z

5


SELECT status,
COUNT(id) AS `total`
FROM your_table
GROUP BY status
ORDER BY total DESC

answered Feb 2, 2012 at 8:07

Sudhir Bastakoti

100k15 gold badges163 silver badges169 bronze badges

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

1 Answer 1