mysql - 'where' 放在哪里？

Question

我有以下查询:

select 
sites.denomination,
rooms.floor,
rooms.number,
(count(beds.id) - sum(if(beds.state<0,1,0))) as active
from rooms
join sites on rooms.site_id = sites.id
join beds on rooms.id = beds.room_id group by beds.room_id
order by
    sites.denomination,
    rooms.floor

它运行良好，但我想在网站上添加一个选择(字段 sites.id)。我总是收到错误消息:“您的 SQL 语法有错误；请查看手册...”

谢谢!

Answer 1

SELECT
sites.denomination,
rooms.floor,
rooms.number,
(COUNT(beds.id) - SUM(if(beds.state<0,1,0))) AS active
FROM rooms
JOIN sites ON rooms.site_id = sites.id
JOIN beds ON rooms.id = beds.room_id 
//your WHERE should be here
GROUP BY beds.room_id
ORDER BY
    sites.denomination,
    rooms.floor