存在しない行も含めてカウント

Question

本日の各部屋の空き状況を求める SQL を作っています。
（かなりデフォルメしています）

DROP TABLE IF EXISTS facilities, pc_logs;

CREATE TABLE facilities(
    id          INT     --  部屋番号
  , room_name   TEXT    --  部屋の名前
  , capacity    INT     --  収容人員
);
INSERT INTO facilities(id , room_name , capacity) VALUES
    (1, 'r1', 40), (2, 'r2', 50), (3, 'r3', 30);
SELECT * FROM facilities;
 id | room_name | capacity
----+-----------+----------
  1 | r1        |       40
  2 | r2        |       50
  3 | r3        |       30
(3 行)

CREATE TABLE pc_logs(
    id          INT         --  連番
  , room_id     INT         --  部屋番号
  , login       TIMESTAMP   --  ログイン時刻
);
INSERT INTO pc_logs( id , room_id , login) VALUES
    (1, 1, '2015/06/01 09:00:00'),
    (2, 2, '2015/06/02 09:10:00'),
    (3, 2, '2015/06/02 09:20:00'),
    (4, 2, '2015/06/02 09:30:00'),
    (5, 2, '2015/06/02 09:40:00');
SELECT * FROM pc_logs;
 id | room_id |        login
----+---------+---------------------
  1 |       1 | 2015-06-01 09:00:00
  2 |       2 | 2015-06-02 09:10:00
  3 |       2 | 2015-06-02 09:20:00
  4 |       2 | 2015-06-02 09:30:00
  5 |       2 | 2015-06-02 09:40:00
(5 行)

求める結果：

 facility_id | room_name | occupied | capacity
-------------+-----------+----------+----------
           1 | r1        |        0 |       40
           2 | r2        |        4 |       50
           3 | r3        |        0 |       30
(1 行)

書いてみた SQL：

SELECT
    facilities.id           AS  facility_id  --  部屋番号
  , facilities.room_name    AS  room_name    --  部屋の名前
  , COUNT(pc_logs.*)        AS  occupied     --  利用者数
  , facilities.capacity     AS  capacity     --  収容人員
FROM    facilities
LEFT OUTER JOIN pc_logs     ON(facilities.id = pc_logs.room_id)
WHERE   login >= '2015/06/02'
GROUP BY facility_id, room_name, capacity
ORDER BY facility_id
;
 facility_id | room_name | occupied | capacity
-------------+-----------+----------+----------
           2 | r2        |        4 |       50
(1 行)

WHERE 句が先に適用されるためこうなってしまうんだと思うのですが、
どう書けばよいか教えてください。

Answer 1

変更前
WHERE login >= '2015/06/02'
変更後
AND pc_logs.login >= '2015/06/02'

ではいかがでしょう？
結合先データで存在しないデータになっていれば
LEFT OUTER JOINでない項目がNULLで結合するはずです

Comments

[hotta]

ON(条件) の後に AND で追加条件を書けるんですね。。
ありがとうございました。