Sum for group by if... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum for group by if...

Author

Topic

Kyriakos
Starting Member

13 Posts

Posted - 2007-10-28 : 14:20:11

For a set like

REGION SALESMAN LINE OF SALES AMOUNT
A 1 Q 12
A 2 R 13
A 3 R 12
A 3 Q 11
A 1 S 9
A 2 Q 12
A 1 S 11
A 3 R 5
A 1 S 9
A 2 Q 12
B 4 S 11
B 6 S 11
B 4 Q 23
B 4 S 3
B 7 S 21
B 6 Q 12
B 5 S 14
B 5 Q 12
B 5 R 11

I shall need to group by salesman but if the line of sales is Q, for all salesmen that belong to A region, their sales should be added to salesman #2 and for all salesmen that belong to B region, their sales should be added to salesman 6
The output should be like

REGION SALESMAN LINE OF SALES AMOUNT
A 1 Q 0
A 1 S 29
A 2 R 13
A 2 Q 35
A 3 R 17
A 3 Q 0
B 4 S 14
B 4 Q 0
B 5 S 14
B 5 Q 0
B 5 R 11
B 6 S 11
B 6 Q 47
B 7 S 21

Thanks for any help in advance

Kyriakos

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-10-28 : 14:29:49

Select Region,
Case
When [Line of Sales] = 'Q' and Region = 'A' then 2
When [Region] = 'B' then 6 else [Salesman] end as Salesman,
[Line of Sales], Sum(Amount) as SumAmount
From [TableName]
Group by Region,
Case
When [Line of Sales] = 'Q' and Region = 'A' then 2
When [Region] = 'B' then 6 else [Salesman] end,
[Line of Sales]

Untested.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources