Postgresql, combine different columns counts into one result?-CodePudding

I have Car table. Car has is_sold and is_shipped. A Car belongs to a dealership, dealership_id (FK).

I want to run a query that tells me the count of sold cars and the count of shipped cars for a given dealership all in one result.

sold_count | shipped_count

10 | 4

The single queries I have look like this:

select count(*) as sold_count
from car
where dealership_id=25 and is_sold=true;

and

select count(*) as shipped_count
from car
where dealership_id=25 and is_shipped=true;

How do I combine the two to get both counts in one result?

CodePudding user response：

You can use the filter clause of the Aggregate function. (see demo)

select dealership_id
     , count(*) filter (where is_sold)    cars_sold
     , count(*) filter (where is_shipped) cars_shipped
  from cars 
 where dealership_id = 25
 group by dealership_id;

CodePudding user response：

This will do:

select dealership_id,
        sum(case when is_sold is true then 1 else 0 end),
        sum(case when is_shipped is true then 1 else 0 end)
 from cars group by dealership_id;