How to get the count of column value in postgresql

Question

I have two tables First one with Status, deviceSerial columns.Status be in (1,2,3,4,5). I want to get the result as count of status

for example device one want to get how many records in status 1, status 2, status 3, status 4, status 5 in a single row

here is my first table

here is my second table

I want to get these records to result as

Here is the code I tried

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"

what is the mistake I have done in this problem,please help me

Tim Biegeleisen · Accepted Answer · 2019-07-29 06:33:37Z

3

Use pivoting logic:

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;

answered Jul 29, 2019 at 6:33

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gordon Linoff Over a year ago

@SupunAravinda . . . This is also slightly faster in Postgres, and (I think) a clearer way of expressing the logic. Note that FILTER is not a Postgres extension; it is standard SQL but Postgres is (probably) the first database to implement it.

Fahmi · Accepted Answer · 2019-07-29 06:31:14Z

2

You can try below using conditional aggregation

select deviceserial,
       count(case when statusid=1 then 1 end) as Permitted ,
       count(case when statusid=2 then 1 end) as IssuedToday,
       count(case when statusid=3 then 1 end) as Permitted ,
       count(case when statusid=4 then 1 end) as InvalidVehicle,
       count(case when statusid=5 then 1 end) as ExpiredOrNotPaid ,
       count(case when statusid=6 then 1 end) as Failed ,
       count(case when statusid=7 then 1 end) as Other
from firstTtable
group by deviceserial

answered Jul 29, 2019 at 6:31

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

Collectives™ on Stack Overflow

How to get the count of column value in postgresql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related