Sum(A) by B but use the first row for C?

Question

Post reply

Sum(A) by B but use the first row for C?

sol.nt

SSCrazy

Points: 2062
More actions
October 12, 2009 at 1:53 am

#216253

Hi all,
Assume that i have the table as follow:
Table Tmp,
UserID , Num1 , Num2
A 10 15
A 20 25
C 30 35
and i have statement like this:
select userid , sum(Num1)
from Tmp
group by userid
result is:
A 30
C 30
but the problem is i want using value of the first row of column Num2,
select userid , sum(Num1) , Num2
from Tmp
group by userid
result
UserID , Num1 , Num2
A 10 15
A 20 25
C 30 35
what i expected is
UserID , Num1 , Num2
A 30 15
C 30 35
So, how do i do this?
Thanks
Sol

Viewing 7 posts - 1 through 6 (of 6 total)

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

arun.sas SSChampion Points: 11831 More actions · Answer 1

Check with these statements, is any performance cross, you update last column in the separate statement.

select a.userid , sum(a.Num1)

,(select top 1 b.Num2 from Tmp b where b.userid = a.userid) as Num2

from Tmp a

group by a.userid

Mark Cowne One Orange Chip Points: 26944 More actions · Answer 2

> using value of the first row of column Num2

You'll have to explain what "first" row means, but see if this helps

select userid , sum(Num1) ,min(Num2)

from Tmp

group by userid

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

arun.sas SSChampion Points: 11831 More actions · Answer 3

Hi mark,

The sample data may be like this

create table #temp

(

uid1 varchar(5),

col1 int,

col2 int

)

insert into #temp

select 'A',10,15

union all

select 'A',15,25

union all

select 'B',25,10

union all

select 'B',35,15

union all

select 'C',50,25

sol.nt SSCrazy Points: 2062 More actions · Answer 4

@arun.sas:

thanks for your solution :), i checked performance of this method and my method, it is nearly the same(around 2mil records)

i just checked the time which the query return , no more 🙂

@Mark-101232:

The "first row" means the row which come first in a group.

In my sample, that is the first row of "A" group.

About some syntax when post new thread, thanks for mention it to me, i will follow that.

Regards,

Sol

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 5

sol-356065 (10/12/2009)
@Mark-101232:
The "first row" means the row which come first in a group.
In my sample, that is the first row of "A" group.

SQL does not have a "first" row. Sets do not have any order.

You need to apply an order to the data in order to determine what "first" is. In you example, you would need to order by Col1 and/or Col2 in order to get the "first" for A-group. So, the question is more about how to do you order your data to determine which row is actually the "first" row.

sol.nt SSCrazy Points: 2062 More actions · Answer 6

Lamprey13 (10/14/2009)

sol-356065 (10/12/2009)
SQL does not have a "first" row. Sets do not have any order.
You need to apply an order to the data in order to determine what "first" is. In you example, you would need to order by Col1 and/or Col2 in order to get the "first" for A-group. So, the question is more about how to do you order your data to determine which row is actually the "first" row.

Yes, You're right. I did not explain well for my example. What i mean is value of the row that i want in a group, maybe that is the first row or the last row( order by something ).

Regards

Sol