MySQL分组汇总

Question

在我的成员(member)表中，我想要“位置”1 - 7 中所有不同人员的摘要，以及有多少人在线和离线。

SELECT location, COUNT(*) FROM members GROUP BY location;

返回:

1 10  
2 5  
3 4  
4 12  
5 6  
6 3  
7 19  

我想要 COUNT 个状态为 0(离线)和状态为 1(在线)的成员。我该怎么做？

Answer 1

SELECT location, status, COUNT(*) FROM members GROUP BY location, status

作为一行:

SELECT
    location,
    COUNT(status) - SUM(status) as offline,
    SUM(status) as online
FROM members
GROUP BY location