Блог

Я получил две таблицы, одна из которых является member, другая — member_status таблица. Мне нужно получить количество элементов с одинаковым статусом.

Участник

  ---- ------------- ----------- --------- -------- -------- -------------- --------- --------------------- --------------------- 
| id | full_name   | mobile_no | address | status | remark | edit_user_id | user_id | created_at          | updated_at          |
 ---- ------------- ----------- --------- -------- -------- -------------- --------- --------------------- --------------------- 
|  1 | John Doe    | 123       |         | 1      |        |            1 |       1 | 2014-06-19 15:51:08 | 2014-06-19 15:51:08 |
|  2 | Michael Bay | 123       |         | 1      |        |            1 |       1 | 2014-06-19 15:51:08 | 2014-06-19 15:51:08 |
|  3 | Hey Hey     | 123       |         | 3      |        |            1 |       1 | 2014-06-19 15:51:08 | 2014-06-19 15:51:08 |
 ---- ------------- ----------- --------- -------- -------- -------------- --------- --------------------- --------------------- 
  

member_status

  ---- --------------------- ---------------------- -------- -------------- --------- --------------------- --------------------- 
| id | name                | description          | status | edit_user_id | user_id | created_at          | updated_at          |
 ---- --------------------- ---------------------- -------- -------------- --------- --------------------- --------------------- 
|  1 | Visitor             | Visitor              | 1      |            1 |       1 | 2014-06-19 15:51:08 | 2014-06-19 15:51:08 |
|  3 | Member              | Member               | 1      |            1 |       1 | 2014-06-19 15:51:08 | 2014-06-19 15:51:08 |
|  4 | Level 1             | Level 1              | 1      |            1 |       1 | 2014-06-19 15:51:08 | 2014-06-19 15:51:08 |
| 11 | Level 12            | Level 12             | 1      |            1 |       1 | 2014-07-03 17:27:00 | 2014-07-03 17:27:00 |
 ---- --------------------- ---------------------- -------- -------------- --------- --------------------- --------------------- 
  

Я пробовал этот запрос, но это НЕВЕРНЫЙ вывод…

 select `member_status`.`name`, count(*) as totalNum
from member
right join `member_status` on (member.`status` = `member_status`.id) 
group by `member_status`.name
  

Это результат, который я хотел бы иметь

  --------------------- ---------- 
| name                | totalNum |
 --------------------- ---------- 
| Level 1             |        0 |
| Level 12            |        0 |
| Member              |        1 |
| Visitor             |        2 |
 --------------------- ---------- 
  

Однако, каким-то образом, результат такой

  --------------------- ---------- 
| name                | totalNum |
 --------------------- ---------- 
| Level 1             |        1 |
| Level 12            |        1 |
| Member              |        1 |
| Visitor             |        2 |
 --------------------- ---------- 
  

Кто-нибудь может дать совет?

Попробуйте
select * from member right join member_status on (member.status = member_status.id)
и вы увидите NULL-s для полей-членов. Вот почему вы получаете больше значений, чем хотите.

Одно из решений:
select member_status.name, count(member.id) as totalNum
from member right join member_status on (member.status = member_status.id)
group by member_status.name

или

select member_status.name, sum(case when member.id is null then 0 else 1 END) as totalNum
from member right join member_status on (member.status = member_status.id)
group by member_status.name

SELECT S.NAME, SUM(CASE WHEN S.ID = T.STATUS THEN 1 ELSE 0 END )
FROM member_status S, member T
GROUP BY S.NAME

Для такого результата вам нужен коррелированный подзапрос:

 select      member_status.name, m.m_qty AS totalNum

from        member_status
left join   (
                select      member.status, count(member.id) AS m_qty
                from        member
                group by    member.status
            ) AS m ON (m.status = member_status.id) 
  

Как насчет этого :

 select `member_status`.`name`, CASE WHEN member.`status` IS NULL THEN 0 ELSE count(*) as totalNum END
from member
right join `member_status` on (member.`status` = `member_status`.id) 
group by `member_status`.name, member.`status`