Multiple Group by clauses - 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 2005 Forums

Transact-SQL (2005)

Multiple Group by clauses

Author

Topic

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-06 : 10:27:33

Hello All,

I have to group by a column based on the contype column in the table to SUM the total. This can be done using temp tables but is there any other way to do the same.

If ConType is 1 then group by PO column
If ConType is 2 then group by OR column
If ConType is 3 then group by IN column
Else group by Misc column to add the Amounts

Here is the sample data.

CustNo ConType PO OR IN Misc Amount
A 1 P10 O120 I990 M1 200.00
A 1 P10 - I991 M1 100.00
A 1 P20 O120 I993 M2 50.00
A 2 P10 O120 I990 M3 20.00
A 2 P05 O120 I995 M4 40.00
A 3 P07 O134 I400 M4 30.00
A 0 - O129 I999 M5 60.00
A 0 P05 O120 I995 M6 10.00

The result set should look like this
CustNo Amount PO OR IN Misc
A 300.00 P10
A 50.00 P20
A 60.00 O120
A 30.00 I400
A 60.00 M5
A 10.00 M6

Please let me know if it not clear because it was difficult for me to think and write it.

Thanks in advance,
-S

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-06 : 11:08:19

Any suggestions or help on this post would be greatly appreciated.

Thanks,
-S

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-05-06 : 11:11:37

you can put a CASE in the group by. are you ok to write it or do you need guidance?

Em

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-06 : 11:14:00

Clear Sample


Here is the sample data. 

CustNo ConType PO   OR    IN    Misc Amount
A       1      P10  O120  I990  M1   200.00 
A       1      P10    -   I991  M1   100.00
A       1      P20  O120  I993  M2    50.00
A       2      P10  O120  I990  M3    20.00
A       2      P05  O120  I995  M4    40.00
A       3      P07  O134  I400  M4    30.00
A       0       -   O129  I999  M5    60.00
A       0      P05  O120  I995  M6    10.00

The result set should look like this
CustNo Amount   PO   OR   IN   Misc
A      300.00   P10 
A       50.00   P20
A       60.00        O120
A       30.00             I400
A       60.00                   M5
A       10.00                   M6

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2009-05-06 : 11:30:16

try


Select CustNo,sum(Amount) as Amount,[PO OR IN], MISC
(select CustNO,Amount,[PO] as [PO OR IN],MISC
from 
MYTABLE
Union ALL
select CustNO,Amount,[OR],MISC
from 
MYTABLE
Union All
select CustNO,Amount,[In],MISC
from 
MYTABLE
) a
group by a.CustNo,a.[PO OR IN],MISC

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-06 : 13:07:34

Em,

Can you give me a sample of using CASE statements with my query.

Thanks,
-S

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-05-07 : 03:51:53

like this....?


declare @t table (CustNo char(1),ConType int, [PO] varchar(5), [OR] varchar(5), [IN] varchar(5),[Misc] varchar(5), [Amount] money)

insert into @t
select 'A',1,'P10',  'O120',  'I990',  'M1',   200.00 union all
select 'A',1,'P10',    '-',   'I991',  'M1',   100.00 union all
select 'A',1,'P20',  'O120',  'I993',  'M2',    50.00 union all
select 'A',2,'P10',  'O120',  'I990',  'M3',    20.00 union all
select 'A',2,'P05',  'O120',  'I995',  'M4',    40.00 union all
select 'A',3,'P07',  'O134',  'I400',  'M4',    30.00 union all
select 'A',0,'-',   'O129',  'I999',  'M5',   60.00 union all
select 'A',0,'P05', 'O120',  'I995',  'M6',    10.00 


select * from @t

select custno, SUM(amount) as [Amount]
,case when ConType = 1 then [PO] else '' end as [PO]
,case when ConType = 2 then [OR] else '' end as [OR]
,case when ConType = 3 then [IN] else '' end as [IN]
,case when ConType = 0 then [Misc] else '' end as [Misc]
from @t
group by CustNo
,case when ConType = 1 then [PO] else '' end 
,case when ConType = 2 then [OR] else '' end
,case when ConType = 3 then [IN] else '' end 
,case when ConType = 0 then [Misc] else '' end 


/*

custno	Amount	PO	OR	IN	Misc
A	60.00				M5
A	10.00				M6
A	30.00			I400	
A	60.00		O120		
A	300.00	P10			
A	50.00	P20			
*/

sqlpal2009
Yak Posting Veteran

58 Posts

Posted - 2009-05-07 : 16:16:48

Thanks Em,

One thing I fogot to mention was, there is another column in my group by addressid. This also is conditional. Means check the address at the Order level if it is not there then Quote level and if it is not there then use the customer default. If there are more tahn one quotes a the Order level then take the addressid from maximum QuoteID.

So I have actually ended up creating temp # tables for each ConType. But now how would I built the logic for addressids.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-05-11 : 03:48:52

can you demonstrate it again? i'm not really following it from your description

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources