mysql查询显示组数据

Question

下面有这个SQL

数据

CREATE TABLE data
    (id int, name varchar(9), room varchar(9), paid varchar(9));

INSERT INTO data
    (id, name, room, paid)
VALUES
    (1, 'jane', 'room1','0'),
    (2, 'any', 'room2', '1'),
    (3, 'mandy', 'room2', '1'),
    (4, 'vivian', 'room2', '1'),
    (5, 'amber', 'room3', '1'),
    (6, 'fany', 'room4', '1'),
    (7, 'diana', 'room4', '0');

查询

SELECT 
  data.room, COUNT(data.id) as total
FROM 
  data
WHERE 
  data.paid = 0
GROUP BY
  data.room

示例链接 sqlfiddle

现在的结果是

room 1 = 1 
room 4 = 1

我想要的结果

room 1 = 1
room 2 = 0
room 3 = 0
room 4 = 1

Answer 1

SELECT 
  data.room, SUM(data.paid = 0) as total
FROM 
  data
GROUP BY
  data.room;

查看 Demo on SQL Fiddle .