Select and Group by sub select query

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

Select and Group by sub select query

Author

Topic

Stan1978
Starting Member

31 Posts

Posted - 2011-09-09 : 07:47:24

I have this query which works correctly.

select
left(Transid,6) as Month,

sum((case when dbo.Trans.TransType = 'Deposit' then DeltaCash else 0 end)* dbo.Trans.BookRate) as RealDeposit,

(SELECT dbo.Property.[Value] FROM dbo.Property WHERE dbo.Property.AccountID = dbo.Trans.AccountID
and dbo.Property.Property = 'Country') AS Country

from dbo.Trans

group by left(Transid,6),
dbo.Trans.AccountID

This issue I have is there are 100's of countries. Each accountid only has one country. I want to be able to group by my major demographics.

Where country = DK,SE and then group every other country as ROW.

Many thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 07:53:20

do you mean this?


select dbo.Property.[Value],
left(Transid,6) as Month,

sum((case when dbo.Trans.TransType = 'Deposit' then DeltaCash else 0 end)* dbo.Trans.BookRate) as RealDeposit
from dbo.Trans
join dbo.Property 
on dbo.Property.AccountID = dbo.Trans.AccountID
and dbo.Property.Property = 'Country'
group by dbo.Property.[Value],
left(Transid,6),
dbo.Trans.AccountID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Stan1978
Starting Member

31 Posts

Posted - 2011-09-09 : 08:03:07

The issue i have is there are 100 countries in the DB - I need to group by individual countries Dk, SE but then I want all other countries to be grouped together and called ROW. I hope this makes sense thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 08:14:26

you mean you need a total row in the end for all countries together?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Stan1978
Starting Member

31 Posts

Posted - 2011-09-09 : 08:16:11

Sorry for my poor explanation - i want 3 result rows, Dk,SE,ROW - where ROW is the total of all countries except Dk,SE.

I hope thats clearer thanks for your time.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 08:20:00

this will give you three row result 1 each for Dk,SE and all others together as ROW

select CASE WHEN dbo.Property.[Value] IN ('Dk','SE') THEN dbo.Property.[Value] ELSE 'ROW' END AS Country,
sum((case when dbo.Trans.TransType = 'Deposit' then DeltaCash else 0 end)* dbo.Trans.BookRate) as RealDeposit
from dbo.Trans
join dbo.Property 
on dbo.Property.AccountID = dbo.Trans.AccountID
and dbo.Property.Property = 'Country'
group by CASE WHEN dbo.Property.[Value] IN ('Dk','SE') THEN dbo.Property.[Value] ELSE 'ROW' END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Stan1978
Starting Member

31 Posts

Posted - 2011-09-09 : 09:25:13

Great thank you works as expected. Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-09 : 10:18:03

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources