I have a table like this:
Date Product
1/1/2015 Apples
1/1/2015 Apples
1/1/2015 Oranges
1/2/2015 Apples
1/2/2015 Apples
1/2/2015 Oranges
How can I do a select so I get something like this:
Date Count of Apples Count of Oranges
1/1/2015 2 1
1/2/2015 2 1
Thanks. I have tried case like this but the error is being thrown:
Select 'Date',
CASE WHEN 'Product' = 'Apples' THEN COUNT(*) ELSE 0 END as 'Count'
FROM #TEMP Group by 1,2
Each GROUP BY expression must contain at least one column that is not an outer reference.
You can do conditional aggregation like this:
select
[date],
sum(case when Product = 'Apples' then 1 else 0 end) as [Count of Apples],
sum(case when Product = 'Oranges' then 1 else 0 end) as [Count of Oranges]
from #temp
group by [date]
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments