use group by with case - 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

SQL Server 2008 Forums

Transact-SQL (2008)

use group by with case

Author

Topic

ayadav0984
Starting Member

21 Posts

Posted - 2011-07-27 : 03:31:30

i have a query to sum :
select top 5 brand,
isnull(sum(
case when TrnxMnth='JUN-11' and current_status='order shipped' and product_cat='APPAREL' and channel not in ('web','web ie','web cc','web pp')then suborderamount end)/100000,0) as [GSV_JUN-11],
isnull(sum(case when TrnxMnth='JUN-11' and current_status='order shipped' and product_cat='APPAREL' and channel not in ('web','web ie','web cc','web pp')then MIS_Margin end)/100000,0)as [MARGIN_JUN-11],
isnull(sum(case when TrnxMnth in ('MAY-11') and current_status='order shipped' and product_cat='APPAREL' and channel not in ('web','web ie','web cc','web pp')then suborderamount end)/100000,0) as [GSV_MAY-11],
isnull(sum(case when TrnxMnth in ('MAY-11') and current_status='order shipped' and product_cat='APPAREL' and channel not in ('web','web ie','web cc','web pp')then MIS_Margin end)/100000,0) as [MARGIN_MAY-11]
from dbo.COMMISSION_DB_APR10_JUN11 where brand is not null group by brand order by [GSV_JUN-11] desc

what i require in the output is another 15th row which contains the substraction of the total sum minus the sum of top 14 brands

ayadav0984
Starting Member

21 Posts

Posted - 2011-07-27 : 03:35:52

the report should be in format:
brand gsv margin
AKAI
HCL ME
HP
SANDISK
STRONTIUM
Others

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources