当前位置: 动力学知识库 > 问答 > 编程问答 >

mysql - Count user's messages with 1 query for 2 tables

问题描述:

There are 2 tables:

  1. users
  2. messages

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
分享给朋友:
您可能感兴趣的文章:
随机阅读: