我需要计算每种类型的消息表中未读消息的数量。
表:消息
领域:
Id Message Type Unread
1 xxxxx 0 0
2 xxxxx 1 0
3 xxxxx 1 0
4 xxxxx 1 1
5 xxxxx 2 0
6 xxxxx 3 0
7 xxxxx 3 1
所以,我需要这样一个结果:
For type 0 there is 1 unread message, and a total of 1 message.
For type 1 there are 2 unread messages, and a total of 3 messages.
For type 2 there is 1 unread message, and a total of 1 message.
For type 3 there is 1 unread message, and a total of 2 messages.
到目前为止,我能够计算每种类型有多少条消息:
SELECT
`message_type`,
COUNT(`message_type`) AS message_type_count
FROM
Messages
GROUP BY `message_type`
但是,我还需要每种类型的未读邮件数量。我该怎么做?
谢谢你的帮助!
最佳答案
SELECT message_type,
COUNT(message_type) AS message_type_count,
SUM(Unread = 1) AS unread_count
FROM Messages
GROUP BY message_type