时间戳

首页 > 解决方案 > 我有一个类似于以下的表格。我想要不同状态的单独计数可用

mysql - 我有一个类似于以下的表格。我想要不同状态的单独计数可用

问题描述

表数据

id |name     | status         |location 
----------------------------------
 1 |ABCD     |completed       | Delhi
 2 |QWER     |construction    | Mumbai
 3 |RBVF     |To_launch       | Chennai
 4 |POIU     |ready           | Kolkata
 5 |WABCD    |completed       | Delhi
 6 |RQWER    |construction    | Mumbai
 7 |TRBVF    |To_launch       | Chennai
 8 |UPOIU     |ready           | Kolkata
 9 |RBVF      |To_launch       | Chennai
 10|POIU      |ready           | Kolkata
 11|WABCD     |completed       | Delhi

可以说我的输出应该是

construction status      count
completed                  4
To_launch                  3
ready                      3
construction               2

我试过类似的东西

select d.status, count(d.status)
from data d
where d.status = 'completed'

但我希望所有状态结果都出现在一个查询中。但我无法弄清楚。TIA ..请建议在 SQL 中改进自己。

标签： mysqlsql

解决方案

使用聚合函数count 和group by子句

   select status as `construction status` ,count（*）as Cnt from Yourtable
    group by status

下面的查询还显示了您评论中想要的总数

   select status as `construction status`, count(*) as count, total from  
   Yourtable,(select count(*) as total from Yourtable) t2 
   group by status

http://www.sqlfiddle.com/#!9/8abcce/4

推荐阅读