dcsimg
Results 1 to 9 of 9
  1. #1

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,275

    sum all values per id

    Strangely enough I cannot find any straightforward example.
    In short, I have
    id value
    1 2
    1 3
    1 1
    2 1
    2 2

    .
    What I need to do is sum in a column with the total of the id's
    so:
    id value valuesum
    1 2 6
    1 3 6
    1 1 6
    2 1 3
    2 2 3

    How can I do that on a table?
    Do i need to join the same table or use a partition?
    Actually I have a CTE table so I probably would need something more than a single table example but what is the simplest I can do?
    I rather see a simple example and if I have issues then I would post more T-Sql.

    Thanks.
    Slow as hell.

  2. #2
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: sum all values per id

    I think you should be able to use a partition but I'm not in a position to test it. Something like:-

    Code:
    Select ID, Value, Sum(Value) Over (Partition By ID) as ValueSum
    From MyTable
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  3. #3

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,275

    Re: sum all values per id

    Yeah I tried this but it somehow gives me wrong values (for example the sum is 10+10+10 and the values sum gives 22,5 )
    Probably tired and doing something wrong..
    Is this a correct conversion?
    sum(VDWC.PaidGrossValue * cast(VDWC.Quantity as float)) Over (Partition By VDWC.TransNumber) as ValueSum
    thanks.

    Will see it extensively tomorrow. The good part is that I am calling this in a list of(t) so at worst I will just add inside vb.
    Slow as hell.

  4. #4
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,653

    Re: sum all values per id

    I would just use a standard sub query

    Code:
    Select F1
          ,F2
          ,(Select Sum(STx.F2) From SomeTable STx Where STx.F1=ST.F1)
          from SomeTable ST

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  5. #5
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: sum all values per id

    I would just use a standard sub query
    Dude, that is soooo, like, 1990s

    just knocked up the following script and both approaches work fine:-
    Code:
    create table #test (id int, val int)
    insert into #test
    Values (1,3),
    		(1,2),
    		(1,5),
    		(2,6),
    		(2,3)
    
    
    Select id,
    		val,
    		sum(val) over (partition by id)
    From #test
    
    
    Select id,
    		val,
    		(Select SUM(val)
    		From #test test2
    		Where test2.id = test1.id)
    From #test test1
    Interestingly (and quite surprisingly, if I'm honest), the sub query form performed better, representing just 32% of the batch rather than the 68% the window function created. I'll admit it isn't a realistic sample size, though. I'd want a much bigger sample size before I came to a proper opinion but the sub query seems faster at a glance.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  6. #6
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,653

    Re: sum all values per id

    I'm thinking that the sub-query method is so fully used by us coders that the engine by now must be smart enough to pre-prepare and entire working set of the sub-query table collapsed by the key field - in advance of the merging of that with the final result set.

    I like my 1990's!

    Oddly enough the syntax of your first query suggests you are asking it to produce the same initial working set

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  7. #7
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    6,772

    Re: sum all values per id

    Oddly enough the syntax of your first query suggests you are asking it to produce the same initial working set
    Yeah, they should be logically identical but...

    I'm thinking that the sub-query method is so fully used by us coders that the engine by now must be smart enough to pre-prepare and entire working set of the sub-query table collapsed by the key field - in advance of the merging of that with the final result set.
    Conventional wisdom is that a corelated sub query has to be run for each row individually which is why I was expecting a slower result. I was in a rush, though, so didn't actually look at the execution plans. I just noted that they were different and that the sub query had a lower cost relative to the batch.

    I'm feeling pretty curious about that so I might knock up a bigger sample and try again, looking at what's really happening under the lid. I wonder how an index on ID would affect it too.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  8. #8
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    CT
    Posts
    17,653

    Re: sum all values per id

    With a small sample of data the "initial" setup time for a particular branch of logic becomes predominant. The SUM/Partition syntax suggests that there are more flavors of attack with this newer construct - which could lead to more "initial" setup time.

    I might also do some testing here - although this is turning out to be a busy fargin' week!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  9. #9

    Thread Starter
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,275

    Re: sum all values per id

    I decided to go with vb.net programming because the specs changed.
    But if you are interested, here is a part of what I needed to change on SQL.
    Code:
    ---Temp table insert CM---
    with tickets
    (ClubName,ClubID,CardNumber,MembershipID,FirstName,LastName,EMail,
    Cinemacode,Cinemaname,SalesTransWorkstationCode,salestranscinemadate,TypeDescrption,
     showcinemadate,
    admissions,TransNumber,GrossValue
    ,movie,ScreenDescription,TicketorConcession)
    as(select  CM.ClubName,CM.ClubID, CM.CardNumber,CM.MembershipID,CM.FirstName,CM.LastName,CM.EMail,
    T.Cinemacode,T.Cinemaname,T.SalesTransWorkstationCode, T.salestranscinemadate,T.TypeDescrption as [Description],
    T.showcinemadate,
    sum(T.admissions) as admissions,T.TransNumber,sum(T.GrossValue),T.FilmTitleEng,T.ScreenDescription,'T'
    From #tempL CM
    inner join vwDWTickets T on CM.CardNumber = T.LoyaltyMemberCode 
    where T.SalesTransDate between  @datefrom and @dateTo
    and T.LoyaltyMemberName is not null
    and T.TransType = 'TKT'
    and T.TransStatus not in ('C','R')
    and T.TicketTypeHOCode in ('A000000110','A000001455') --only codes
    and email <> ''
    group by
     CM.ClubName,CM.ClubID, CM.CardNumber,CM.MembershipID,CM.FirstName,CM.LastName,CM.EMail,
    T.Cinemacode,T.Cinemaname,T.SalesTransWorkstationCode, T.salestranscinemadate,T.TypeDescrption,
    T.showcinemadate,T.TransNumber
    ,T.FilmTitleEng,ScreenDescription)
    
    select tickets.ClubName,tickets.ClubID,tickets.CardNumber,tickets.MembershipID,tickets.FirstName,tickets.LastName,tickets.EMail,
    tickets.Cinemacode,tickets.Cinemaname,tickets.SalesTransWorkstationCode,tickets.salestranscinemadate,tickets.TypeDescrption as Description,
    tickets.admissions as Transactions,tickets.TransNumber,tickets.GrossValue + IsNull(sum(F.GrossValue), 0) as Value,tickets.showcinemadate
    ,tickets.movie,tickets.ScreenDescription,tickets.TicketorConcession
    from tickets
    left join tblDWBookingFee F on tickets.Transnumber = F.Transnumber and tickets.cinemacode= F.CinemaCode
    
    group by 
    tickets.ClubName,tickets.ClubID,tickets.CardNumber,tickets.MembershipID,tickets.FirstName,tickets.LastName,tickets.EMail,
    tickets.Cinemacode,tickets.Cinemaname,tickets.SalesTransWorkstationCode,tickets.salestranscinemadate,tickets.TypeDescrption,
    tickets.TransNumber,tickets.admissions,tickets.GrossValue,tickets.showcinemadate
    ,tickets.movie,tickets.ScreenDescription,tickets.TicketorConcession
    order by tickets.MembershipID
    
    ---- there is a union here with another table that does not use CTE but the above more complicated
    Every tickets.MembershipID should be showing the tickets.TransNumbers and every transaction (could be more than one with the same id) should have as value.
    That set of values should be calculated in another columns that will give the total of someone membership and his/hers transactions summed.
    I think i would need something like an extra:
    Code:
    ---Temp table insert CM1---
    with tickets2... etc--- valuesum --
     #tempL CM1
    inner join vwDWTickets Tnew  on CM1.CardNumber = Tnew.LoyaltyMemberCode 
    where Tnew.SalesTransDate between  @datefrom and @dateTo
    and Tnew.LoyaltyMemberName is not null  ---etc
    
    select tickets2--- tickets2.Valuesum  -- that is (tickets2.GrossValue + IsNull(sum(F2.GrossValue), 0)) ?
    left join tblDWBookingFee F2 on Tnew.Transnumber = F2.Transnumber and Tnew.cinemacode= F2.CinemaCode
    So It's getting complicated for me so I would just count the iterations in a double in vb and add them.
    But you're welcome to show any suggestion if you like.
    Slow as hell.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width


×
We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.