Put count and group by in one statement

January 4, 2023

I am extracting 3 values from a table, I can extract these values from 3 statements. But I need to put these values in one table so I plan to do it in one statement.

select count(*) from fruit;  
select count(*) from fruit where color = 'red';

select count(*) from fruit 
where color = 'red' and substring(city, 1, 8) = 'New York';

What I am trying to do is similar like this:

select 
    count(*) total_items, 
    (count(*) where color = 'red') red_items, 
    (count(*) where color = 'red' and substring(city, 1, 8) = 'New York') fruit_in_newyork 
from 
    fruit

New Table will have total_items, red_items, fruit_in_newyork as columns.

>Solution :

You could do a conditionally SUM

SELECT count(*) total_items
    ,sum(CASE WHEN color = 'red'
              THEN 1
              ELSE 0
         END) AS red_items
    ,sum(CASE WHEN color = 'red' AND SUBSTRING(city, 1, 8) = 'New York' 
              THEN 1
              ELSE 0
         END) AS fruit_in_newyork
FROM fruit