CODEWITHSUNDEEP
sqlsql-server
Rob
Rob

Reputation: 2472

SQL get count from one table and split to two columns

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.

Upvotes: 2

Views: 1276

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

SQL Server does not accept column references in the GROUP BY. So, "1" and "2" refer to, well, the numbers one and two.

However, you seem to be confusing string constants and columns. Only use single quotes for string and date constants. I suspect that Date and Product are the names of columns. So, the query you probably want is a conditional aggregation:

Select Date,
       SUM(CASE WHEN Product = 'Apples' THEN 1 ELSE 0 END) as NumApples
FROM #TEMP
Group by Date
Order by Date;

Upvotes: 1

jpw
jpw

Reputation: 44881

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]

Upvotes: 6

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

With conditional aggregation:

select date, 
       sum(case when Product = 'Apples' then 1 else 0 end) as Apples,
       sum(case when Product = 'Oranges' then 1 else 0 end) as Oranges,
from table
group by date

Upvotes: 1

Related Questions