sql - 如何在postgresql中获取列值的计数

Question

我有两张 table 第一个状态，deviceSerial columns.Status 在(1,2,3,4,5)。我想得到状态计数的结果

例如设备一想要获取一行中状态1、状态2、状态3、状态4、状态5的记录数

这是我的第一张 table

这是我的第二张 table

我想让这些记录的结果为

这是我试过的代码

Select "deviceSerial",  (select count("statusId")as Permitted from public.scan_track
where "statusId" = 1 group by "statusId"),

(select count("statusId")as IssuedToday from public.scan_track
where "statusId" = 2 group by "statusId"),

(select count("statusId")as PaidForParking from public.scan_track
where "statusId" = 3 group by "statusId"),

(select count("statusId")as InvalidVehicle from public.scan_track
where "statusId" = 4 group by "statusId"),

(select count("statusId")as ExpiredOrNotPaid from public.scan_track
where "statusId" = 5 group by "statusId"),

(select count("statusId")as Failed from public.scan_track
where "statusId" = 6 group by "statusId"),

(select count("statusId")as Other from public.scan_track
where "statusId" = 7 group by "statusId")
from public.scan_track
group by "deviceSerial"

我在这个问题上做错了什么，请帮助我

Answer 1

使用旋转逻辑:

SELECT
    deviceSerial,
    COUNT(*) FILTER (WHERE statusid = 1) AS Permitted,
    COUNT(*) FILTER (WHERE statusid = 2) AS IssuedToday,
    COUNT(*) FILTER (WHERE statusid = 3) AS PaidForParking,
    COUNT(*) FILTER (WHERE statusid = 4) AS InvalidVehicle,
    COUNT(*) FILTER (WHERE statusid = 5) AS ExpiredOnNotPaid,
    COUNT(*) FILTER (WHERE statusid = 6) AS Failed,
    COUNT(*) FILTER (WHERE statusid = 7) AS Other
FROM scan_track
GROUP BY
    deviceSerial;