如何在同一个表中计数,包括零计数值 [英] How to get count in same table including zero count values
问题描述
我有4个请求状态ID(0,1,2,3)
我有这个数据表结构
INSERT INTO`test`(`Requestid`,`RequestStatusID`)VALUES
( 1 ,' 0'),
( 2 ,' 0'),
( 3 ,' 2'),
( 4 ,' 2'),
( 5 ,' 3');
我需要使用此查询计算所有RequestStatusID的计数:
SELECT COUNT(*)AS Counts,RequestStatusID FROM tblRequest GROUP BY RequestStatusID
this返回以下栏目
|计数| RequestStatusID
_________________________________
1 | 2 | 0
_________________________________
2 | 2 | 2
_________________________________
3 | 1 | 3
这有效但是问题是我需要显示所有结果,即使计数结果为0.
这意味着它不显示计数结果为0.
输出应为
|计数| RequestStatusID
_________________________________
1 | 2 | 0
_________________________________
2 | 0 | 1
_________________________________
3 | 2 | 2
_________________________________
4 | 1 | 3
我的尝试:
我需要计算所有RequestStatusID的计数,即使计数结果为0.
你可以试试这个:
我创建了2个不同的表:
SELECT * FROM tblRequestStatus
id RequestStatusID
1 0
2 1
3 2
4 3
SELECT * FROM tblRequestID
Requestid RequestStatusID
1 0
2 0
3 2
4 2
5 3
然后,使用此查询:
选择COUNT(r.RequestStatusID)AS Counts,rs。 RequestStatusID
来自tblRequestStatus rs left join tblRequestID r
on r.RequestStatusID = rs.RequestStatusID
GROUP BY rs.RequestStatusID
得到结果:
计数RequestStatusID
2 0
0 1
2 2
1 3
I Have 4 Request Status ID (0,1,2,3)
I have this table structure with data
INSERT INTO `test` (`Requestid`, `RequestStatusID`) VALUES
(1, '0'),
(2, '0'),
(3, '2'),
(4, '2'),
(5, '3');
I need to count the Counts for all RequestStatusID with this query:
SELECT COUNT(*) AS Counts,RequestStatusID FROM tblRequest GROUP BY RequestStatusID
this return following column
| Counts |RequestStatusID
_________________________________
1 | 2 | 0
_________________________________
2 | 2 | 2
_________________________________
3 | 1 | 3
This works but the problem is that i need to display all results even if the count result is 0.
That means it does not display count result is 0.
The output should be
|Counts |RequestStatusID
_________________________________
1 | 2 | 0
_________________________________
2 | 0 | 1
_________________________________
3 | 2 | 2
_________________________________
4 | 1 | 3
What I have tried:
I need to count the Counts for all RequestStatusID even if the count result is 0.
you can try this:
I have created 2 different tables:
SELECT * FROM tblRequestStatus id RequestStatusID 1 0 2 1 3 2 4 3
SELECT * FROM tblRequestID Requestid RequestStatusID 1 0 2 0 3 2 4 2 5 3
Then, use this query:
select COUNT(r.RequestStatusID) AS Counts,rs.RequestStatusID from tblRequestStatus rs left join tblRequestID r on r.RequestStatusID = rs.RequestStatusID GROUP BY rs.RequestStatusID
to get the result:
Counts RequestStatusID 2 0 0 1 2 2 1 3
这篇关于如何在同一个表中计数,包括零计数值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!