How to add default values to group by & count in PostgreSQL?

Question

is there option to set "default" values to groupBy?

For example I have column cars where values are inserted from enum (BMW, Porsche, Audi itd.).

I would like to group by and count this cars:

select brand, count(*)
from cars
group by brand

If there are values with all of this enum in database, then its ok - results are like this:

BMW | 3
Audi | 4
Jaguar | 5

But if I have in database only cars with BMW result is only BMW:

BMW | 3

and there is my question - how to add default fields to have response like:

BMW | 3
Audi | 0
Mercedes | 0

Gordon Linoff · Accepted Answer · 2021-06-28 11:35:03Z

1

You need to list the values somehow. One method uses a values clause:

select brand, count(c.brand)
from (values ('BMW'), ('Audi'), ('Jaguar')) v(brand) left join
     cars c
     using (brand)
group by brand;

In practice, you should have the brands in their own table, so you could use:

select brand, count(c.brand)
from brands b
     cars c
     using (brand)
group by brand;

answered Jun 28, 2021 at 11:35

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

What in the world is v(brand)?