CODEWITHSUNDEEP
mysqlsql
bingaloman
bingaloman

Reputation: 53

How to group and count in MySQL?

I am trying to write a query which extracts the total number of non-deleted messages sent to their friends. Here is a screenshot of the table structures:

enter image description here

The output result that I am trying to achieve is as below:

 id          messages_count
 ---------------------------
 1            4562
 2            7255

Here is the query that I have tried so far:

SELECT u.id, count( CASE WHEN m.is_deleted = false THEN m.is_deleted END ) as messages_count
FROM users u
inner join messages m on u.id = m.to_id
GROUP BY u.id
ORDER BY u.id;

Upvotes: 0

Views: 68

Answers (3)

AIMIN PAN
AIMIN PAN

Reputation: 1665

Besides Jake White's answer, you can also use sum in this way:

SELECT u.id,
   sum( CASE WHEN m.is_deleted = false THEN 1 else 0 END ) as messages_count
FROM users u
inner join messages m on u.id = m.to_id
GROUP BY u.id
ORDER BY u.id;

it should be clear -- if not deleted, contribute to sum, else not.

Upvotes: 0

BlackCat
BlackCat

Reputation: 19

Use IS keyword in WHERE clause. Try this:

SELECT u.id, count( m.id ) as messages_count
FROM users u
INNER JOIN messages m on u.id = m.to_id
WHERE m.is_deleted is FALSE
GROUP BY u.id
ORDER BY u.id;

Upvotes: 0

Jake White
Jake White

Reputation: 61

Have you tried something along the lines of this?

SELECT u.id, count( m ) as messages_count
FROM users u
inner join messages m on u.id = m.to_id and m.is_deleted = false
GROUP BY u.id
ORDER BY u.id;

Upvotes: 1

Related Questions