Group By problem - 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)

Group By problem

Author

Topic

musman
Starting Member

17 Posts

Posted - 2009-03-18 : 11:33:43

Select col1, col2, col3, col4
from TempTable

Col1 COl2 COL3 COL4 GroupCd1/1/2001 Abc 20.00 xys G1
1/1/2001 EFG 20.00 xys G1
1/1/2001 Abc 20.00 xys NULL
1/1/2001 EFG 20.00 xys NULL
2/1/2001 npk 10.00 xys G1
2/1/2001 Abc 30.00 xys NULL

2/1/2001 Pkk 20.00 xys G1

I need to get sum(col3)of similiar date Grouping by GroupCd
Which is 40 for 1/1/2001 and 30 for 2/1/2001
But I am to get my desired results. What query do I need to execute.

Please help.

Thankx

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-03-18 : 11:44:26

Can you try this..its not tested.

SELECT col1, GroupCd, SUM(col3) from @t
WHERE GroupCd IS NOT NULL
GROUP BY col1,GroupCd

musman
Starting Member

17 Posts

Posted - 2009-03-18 : 11:50:50

I think it would be better if I post actual string

Select InstallNum, RecordWrittenDt, PolicyEffectiveDt, ActivityType, (Case TransactionDes When Null Then TransactionCd Else TransactionDes End) As TransactionCd, ActivityAmt, InstallBalanceAmt,ISNull(ReceiptTypeCd,'') as ReceiptTypeCd,ISNull(ReceiptId,'') as ReceiptId, IsNull(ReceiptTypeDes,'') as ReceiptTypeDes, @TotalRecords As TotalRecords, @FirstRec, @LastRec, TransactionGroupCd-- Added Receipt* fields for # 106707
From #TempTable
WHERE ID > @FirstRec AND ID < @LastRec --and TransactionGroupCd is not Null

Table
coming back is

1 2010-07-03 22:08:56.000 2010-03-10 00:00:00.000 PREM Renewal 2021.10 2021.10 14 0 11 G1
1 2010-07-03 22:08:57.000 2010-03-10 00:00:00.000 PREM Non Premium 1.00 2022.10 14 0 11 G1
1 2010-07-03 22:09:02.000 2010-03-10 00:00:00.000 PREM NULL 4069.20 6091.30 14 0 11 NULL
1 2010-07-03 22:09:04.000 2010-03-10 00:00:00.000 INV Notice 1 6091.30 6091.30 14 0 11 NULL
1 2010-07-17 03:57:08.000 2010-03-10 00:00:00.000 INV NULL 6091.30 6091.30 14 0 11 NULL
1 2010-08-05 23:36:36.000 2010-03-10 00:00:00.000 PREM Cancellation -452.00 5639.30 14 0 11 NULL
1 2010-08-05 23:36:44.000 2010-03-10 00:00:00.000 BILL NULL 5639.30 5639.30 14 0 11 NULL
1 2010-07-02 22:08:00.000 2009-09-10 00:00:00.000 PREM Renewal 1996.00 1996.00 14 0 11 G1
1 2010-07-02 22:08:01.000 2009-09-10 00:00:00.000 PREM Non Premium 1.00 1997.00 14 0 11 G1
1 2010-07-02 22:08:05.000 2009-09-10 00:00:00.000 PREM NULL 2072.20 4069.20 14 0 11 NULL

I need sum of 1.00 + 1996 based on G1(GroupCd) for date 2009-09-10

musman
Starting Member

17 Posts

Posted - 2009-03-18 : 11:51:47

Similarly sum of 2021.10 + 1.00 for date 2009-03-10 based on G1

musman
Starting Member

17 Posts

Posted - 2009-03-18 : 15:42:57

I got it resolved. I did a join with the subset of the results and created new column. and it worked for me.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-19 : 13:09:46

quote:
Originally posted by musman

I got it resolved. I did a join with the subset of the results and created new column. and it worked for me.

no need of join. i think this should give you waht you want


Select InstallNum, RecordWrittenDt, PolicyEffectiveDt, ActivityType, (Case TransactionDes When Null Then TransactionCd Else TransactionDes End) As TransactionCd, SUM(ActivityAmt) OVER (PARTITION BY RecordWrittenDt,TransactionGroupCd), InstallBalanceAmt,ISNull(ReceiptTypeCd,'') as ReceiptTypeCd,ISNull(ReceiptId,'') as ReceiptId, IsNull(ReceiptTypeDes,'') as ReceiptTypeDes, @TotalRecords As TotalRecords, @FirstRec, @LastRec, TransactionGroupCd-- Added Receipt* fields for # 106707 
From #TempTable
WHERE ID > @FirstRec AND ID < @LastRec --and TransactionGroupCd is not Null

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources