CODEWITHSUNDEEP
sqlpostgresqlgroup-by
Punit S
Punit S

Reputation: 3247

Postgres : Group by two columns - group by includes all unique combinations with 0 for null counts

I have a single table called cases as below

os      device      event_time
Android     Mobile      <Tstamp>
Android     Tablet      <Tstamp>
Windows     PC          <Tstamp>
Linux       PC          <Tstamp>

I want an output such as below:

os         device    events_count
Android    Mobile      10
Android    Tablet      22
Android    PC          0
Windows    Mobile      0
Windows    Tablet      0
Windows    PC          40
Linux      Mobile      0
Linux      Tablet      0
Linux      PC          21

The original table does not contain any row with (Android, PC), (Windows, Mobile), etc combinations but I seek the output to have these rows with zero count. The normal group by query doesn't give me those probably because the original table doesn't have these combinations at all.

select os, device, count(event_time)
from cases
group by os, device;

Upvotes: 3

Views: 4702

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

First select all OS / device combinations, then outer join your table again:

select o.os, d.device, count(e.events_count)
from (select distinct os from events) o
cross join (select distinct device from events) d
left join events e on e.os = o.os and e.device = d.device
group by o.os, d.device
order by o.os, d.device;

(For such data you'd usually have an OS table and a device table, though. You'd cross join these and then outer join your event table.)

Upvotes: 4

Radim Bača
Radim Bača

Reputation: 10701

I would use a cross join

select t.os, t.device, count(c.os)
from 
(
  select os, device
  from
  (
    select distinct os from cases
  ) t1
  cross join 
  (
    select distinct device from cases
  ) t2
) t 
left join cases c on t.os = c.os and 
                     t.device = c.device
group by t.os, t.device

Upvotes: 1

Related Questions