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.
CodePudding user response:
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