How to group and count in MySQL?

Question

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:

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;

Answer 1

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;

Answer 2

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;

Answer 3

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.