CODEWITHSUNDEEP
sqlsql-serverpivot
hughjazz
hughjazz

Reputation: 45

sql Group by columns to the same row without join

I have grouped sales from a sales view with sales below using

Select id, name, Count(*) as [Sales], product, amount
from vwSales
Group by
id,name, product, amount


ID  |  Name  | Sales  | Product  | Amount
1   |  Bob   |   4    |  Fridge  |  40 
1   |  Bob   |  12    |  Washer  |  120
2   |  Anne  |   5    |  Fridge  |  50 
2   |  Anne  |   4    |  Washer  |  40

Is it possible to group these in to one row without using a join? So table looks something like

ID  |  Name  | Fridge Sales  |  fridge Amt  | Washer sales | washer amt
1   |  Bob   |    4          |     40       |      12      |   120          
2   |  Anne  |    5          |     50       |      4       |   40

Upvotes: 1

Views: 61

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can do conditional aggregation :

select id, name,
       sum(case when Product = 'Fridge' then 1 else 0 end) as [Fridge Sales],
       sum(case when Product = 'Fridge' then Amount else 0 end) as [fridge Amt], 
       sum(case when Product = 'Washer' then 1 else 0 end) as [Washer Sales],
       sum(case when Product = 'Washer' then Amount else 0 end) as [Washer Amt]
from vwSales
Group by id, name;

Upvotes: 2

Related Questions