Nested count in one Query

Question

I need to count a the amount of unread messages per type of a messages table.

Table: Messages

Fields:

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

So, I need a result like this:

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.

So far, I was able to count how many messages I've per type:

SELECT 
    `message_type`, 
    COUNT(`message_type`) AS message_type_count
FROM 
    Messages
GROUP BY `message_type`

But, I also need the amount of unread messages per type. How can I do that?

Thanks for your help!

your query has a tipo

Sebas
– Sebas

2013-11-11 17:44:17 +00:00
Commented Nov 11, 2013 at 17:44 — Sebas
– Sebas, Commented Nov 11, 2013 at 17:44

juergen d · Accepted Answer · 2013-11-11 17:43:43Z

3

SELECT message_type,
       COUNT(message_type) AS message_type_count,
       SUM(Unread = 1) AS unread_count
FROM Messages 
GROUP BY message_type

answered Nov 11, 2013 at 17:43

juergen d

205k40 gold badges304 silver badges377 bronze badges

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

Collectives™ on Stack Overflow

Nested count in one Query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related