There are 2 tables:
I want count messages that have status=1 and status=2. Here is what i'm trying to do
SELECT u.id_user, u.email, COUNT(m.id_message) as 'viewed', COUNT(m2.id_message) as 'not viewed'
FROM users u
LEFT JOIN messages m ON (u.id_user=m.id_user AND m.status=1)
LEFT JOIN messages m2 ON (u.id_user=m2.id_user AND m2.status=2)
GROUP BY u.id_user
But result is weird
Counters 'viewed' and 'not views' can't be the same. What am i doing wrong?
You may do conditional sum for this
select u.id_user, u.email, sum(case when m.status=1 then 1 else 0 end) as `viewed`, sum(case when m.status=0 then 1 else 0 end) as `not viewed` from users u left join messages m on m.id_user = u.id_user group by u.id_user,u.email