Post reply

Group By trouble

  • March 18, 2009 at 9:43 am

    #132415

    Select col1, col2, col3, col4

    from TempTable

    Col1 COl2 COL3 COL4 GroupCd\

    1/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

  • March 18, 2009 at 9:59 am

    #962050

    I'm not totally clear what you want but you can try this and let me know if it's what's needed

    select col1, sum(col3)

    from TempTable

    where GroupCd is not null

    group by col1

  • March 18, 2009 at 11:14 am

    #962118

    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

    and I need to Add 2021.10 +1.00 which is Renewal and Non Premium based on G1 for similar dates.

  • March 18, 2009 at 1:41 pm

    #962225

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply