Post reply

summing up the gross from the previous rows

  • December 7, 2009 at 10:05 pm

    #217412

    Hi,

    I'm having data like below

    CCID Sno Item Amount

    xx1 1 IA 10.00

    xx1 2 GA 11.00

    AA2 1 FXV 2.00

    AA2 2 DWR 9.00

    AA2 7 SQW 1.00

    WQ1 3 ERE 19.00

    WER 1 234 12.00

    how to produce the follwing output

    CCID Sno Item Amount

    xx1 1 IA 10.00

    xx1 2 GA 11.00

    xx1 3 GA 21.00

    AA2 1 FXV 2.00

    AA2 2 DWR 9.00

    AA2 3 SQW 1.00

    AA2 4 SQW 12.00

    WQ1 1 ERE 19.00

    WQ1 2 ERE 19.00

    WER 1 S234 12.00

    WER 2 S234 12.00

  • December 7, 2009 at 11:22 pm

    #1089030

    Hi, try this

    create table #temp1

    (

    CCID varchar(10),

    Sno int,

    Item varchar(10),

    Amount int

    )

    insert into #temp1

    select 'XX1',1,'AA',10

    union all

    select 'XX1',2,'BB',30

    union all

    select 'XX2',3,'CC',30

    union all

    select 'XX2',4,'DD',40

    union all

    select 'XX2',5,'EE',50

    union all

    select 'XX3',6,'FF',50

    select CCID,Sno,Item,Amount from #temp1

    union all

    select a.CCID,a.Sno+1,b.Item,a.Amount

    from (select CCID,max(Sno)Sno,sum(Amount)Amount

    from #temp1

    group by CCID)a inner join #temp1 b

    on a.CCID = b.CCID

    and a.Sno = b.Sno

    order by CCID,Sno

  • December 8, 2009 at 8:06 am

    #1089255

    From the data provided above. This should work as well:

    select CCID,SUM(Amount) as Amount,case when Sno IS Null then MAX(Sno)+1 else Sno end SNO,MAX(item) as item

    from #temp1

    group by CCID,SNO

    with Rollup

    having CCID is not NULL

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • December 8, 2009 at 11:55 pm

    #1089663

    Hi Arun,

    Thanks for the snippet it works 🙂

    Hi sav-457805,

    Thanks for the code. But, the SNo forms to be a sequential across all the CCID. 🙂

  • December 9, 2009 at 1:41 am

    #1089679

    I am still learning.. Can you please let me know where i am going wrong. Please see below the results from Arun and mine.. They look same to me.

    Arun's Results:

    CCIDSnoItemAmount

    XX11AA10

    XX12BB30

    XX13BB40

    XX23CC30

    XX24DD40

    XX25EE50

    XX26EE120

    XX36FF50

    XX37FF50

    Mine:

    CCIDAmountSNOitem

    XX1101AA

    XX1302BB

    XX1403BB

    XX2303CC

    XX2404DD

    XX2505EE

    XX21206EE

    XX3506FF

    XX3507FF

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • December 9, 2009 at 2:00 am

    #1089683

    Hi,

    Nice approach with rollup, rollup also another way to achieve this requirement, but what the OP say this different, that is, try to compare with arrive result and find.

    insert into #temp1

    select 'XX2',10,'CC',30

    union all

    select 'XX2',4,'DD',40

    union all

    select 'XX2',5,'EE',50

    Then compare with your statement.

  • December 9, 2009 at 2:12 am

    #1089685

    Hi Arun,

    Thank you for the clarification. I can see the difference now. Can the result be achieved by using rollup?

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • December 9, 2009 at 2:58 am

    #1089704

    Hi,

    Yes this also corrected by means of call the sub statement in the rollup like

    Use this data

    select 'XX1',2,'AA',10

    union all

    select 'XX1',1,'BB',30

    union all

    select 'XX2',4,'CC',30

    union all

    select 'XX2',5,'DD',40

    union all

    select 'XX2',3,'EE',50

    union all

    select 'XX3',6,'FF',50

    Your existing statement

    select CCID,

    SUM(Amount) as Amount,

    case when Sno IS Null then MAX(Sno)+1 else Sno end SNO,

    max(item) as item

    from #temp1

    group by CCID,SNO

    with Rollup

    having CCID is not NULL

    sub statement in the rollup

    select a.CCID,

    SUM(a.Amount) as Amount,

    case when a.Sno IS Null then MAX(a.Sno)+1 else a.Sno end SNO,

    (select item from #temp1 b where b.SNO = MAX(a.Sno) and a.CCID = b.CCID)item

    from #temp1 a

    group by a.CCID,a.SNO

    with Rollup

    having a.CCID is not NULL

  • December 9, 2009 at 3:23 am

    #1089714

    Hi Arun

    I am getting this error: when i run the query

    select a.CCID,

    SUM(a.Amount) as Amount,

    case when a.Sno IS Null then MAX(a.Sno)+1 else a.Sno end SNO,

    (select item from #temp1 b where b.SNO = MAX(a.Sno) and a.CCID = b.CCID)item

    from #temp1 a

    group by a.CCID,a.SNO

    with Rollup

    having a.CCID is not NULL

    Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • December 9, 2009 at 3:31 am

    #1089716

    Hi,

    In my desk no errors show yet. Check the data you have.

  • December 9, 2009 at 3:43 am

    #1089723

    This is the data i have after inserting extra 3 records that you mentioned above.

    CCIDSnoItemAmount

    XX11AA10

    XX12BB30

    XX23CC30

    XX24DD40

    XX25EE50

    XX36FF50

    XX210CC30

    XX24DD40

    XX25EE50

    blog: http://sarveshsingh.comTwitter: @sarveshsing

  • December 9, 2009 at 3:49 am

    #1089727

    sav-457805 (12/9/2009)

    This is the data i have after inserting extra 3 records that you mentioned above.

    CCIDSnoItemAmount

    XX11AA10

    XX12BB30

    XX23CC30

    XX24DD40

    XX25EE50

    XX36FF50

    XX210CC30

    XX24DD40

    XX25EE50

    Hi,

    Here is the mistake, your inserting repeated data.

    XX24DD40

    XX24DD40

  • December 9, 2009 at 3:55 am

    #1089732

    Cool. got rid of the duplicates. it works like magic. It's easy when you know. Thank u so much.

    blog: http://sarveshsingh.comTwitter: @sarveshsing

Viewing 13 posts - 1 through 12 (of 12 total)

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