# Thread: sum all values per id

1. ## 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.

2. ## 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

3. ## 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.

4. ## 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

5. ## 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.

6. ## 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

7. ## 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.

8. ## 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!

9. ## 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,
,movie,ScreenDescription,TicketorConcession)
as(select  CM.ClubName,CM.ClubID, CM.CardNumber,CM.MembershipID,CM.FirstName,CM.LastName,CM.EMail,
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.FilmTitleEng,ScreenDescription)

select tickets.ClubName,tickets.ClubID,tickets.CardNumber,tickets.MembershipID,tickets.FirstName,tickets.LastName,tickets.EMail,
,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.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.

#### 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