postgresql - 计算相关表中的行

我有以下查询来收集报表数据：

SELECT COUNT(*) as inspected,
count(*) filter(where status='fail') as failed,
count(*) filter(where status='deficient') as impaired,
count(*) filter(where status='pass') as passed,
device_types.name
FROM inspection_data
INNER JOIN devices ON devices.id=inspection_data.device_id
INNER JOIN device_types ON devices.device_type_id=device_types.id
WHERE inspection_id = 3
GROUP BY device_types.id
ORDER BY device_types.name

这个查询按预期工作（尽管我确信可以进行一些优化）。SQL不是我的强项。问题是我现在想再收集一个汇总数据。我想计算此位置id的设备表中每个设备类型id的数量。
我将尝试绘制数据库表：

|     devices    | device_types | inspection_data |
|:--------------:|:------------:|:---------------:|
| id             | id           | id              |
| device_type_id | name         | inspection_id   |
| location_id    |              | device_id       |
|                |              | status          |

所以当我运行查询时，我收到的结果类似于：

| inspected | failed | impaired | passed | name                       |
|:---------:|:------:|:--------:|--------|----------------------------|
| 6         | 0      | 2        | 4      | Air Sampling Type Detector |
| 9         | 1      | 1        | 7      | Alarm Bell                 |

这真是太棒了。我的问题是，不是所有的设备都必须在检查期间进行检查。例如，假设这个位置实际上有15个“警铃”装置，但是根据上表，作为检查的一部分，只有9个被检查过。如何在该输出中包含另一列，名为“total”，报警铃设备类型的值为15，报告中的每种设备类型的值为15，依此类推？
我希望我已经充分地描述了我要做的事情。我完全不知道如何在不运行第二个查询的情况下完成这项工作，我真的不想这样做，除非绝对有必要，因为这会使代码更加混乱。

最佳答案

我想你想要一个left join。不过，我不确定先放哪张桌子。我最好的猜测是：

SELECT COUNT(*) as total,
       COUNT(id.device_id) as inspected,
       COUNT(id.device_id) filter (where status='fail') as failed,
       COUNT(id.device_id) filter (where status='deficient') as impaired,
       COUNT(id.device_id) filter (where status='pass') as passed,
       dt.name
FROM devices d INNER JOIN
     device_types dt
     ON d.device_type_id = dt.id LEFT JOIN
     inspection_data id
     ON d.id = id.device_id AND
        id.inspection_id = 3
GROUP BY dt.id
ORDER BY dt.name

关于postgresql - 计算相关表中的行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/38863395/