Postgresql, combine different columns counts into one result?

Question

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?

Answer 1

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;

Answer 2

You can also using cross join.

select 'hello' as col1, 'world' as col2; return:

col1  | col2
-------+-------
 hello | world
(1 row)

similarly,

with a as
(
select count(*) as a1 from emp where empid> 5), 
 b as (
select count(*) as a2 from emp where salary > 6000)

select * from a, b;

or you can even apply to different table. like:

 with a as
        (select count(*) as a1 from emp where empid> 5), 
         b as 
        (select count(*) as a2 from ab )
    select * from a, b; 

with a as
    (
        select count(*) as sold_count
        from car
        where dealership_id=25 and is_sold=true
    ),
    b as
    (
    select count(*) as shipped_count
    from car
    where dealership_id=25 and is_shipped=true
    )
select a,b;

further reading: https://www.postgresql.org/docs/current/queries-table-expressions.html.
https://stackoverflow.com/a/26369295/15603477