Sum() - query help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Sum() - query help

Author

Topic

sqldbaa
Starting Member

32 Posts

Posted - 2009-09-22 : 06:39:58

Hi ,

I have a table with the below fields

name total
-------------------------------------------------- -----------
a 0
b 1
a 2
c 3
a 5
a 11
c 4
b 2
c 4

I have to retrieve the rows whose sum(total) > 10 grouped by name
The output needs to be shown as below
name total
-------------------------------------------------- -----------
a 0
a 2
a 5
a 11
c 4
c 4
c 3

Can anyone let me know the query for this.

sumitbatra1981
Starting Member

17 Posts

Posted - 2009-09-22 : 06:42:00

select * from table group by name having sum(total)>10

Regards,
Sumit Batra
Software Engineer

sqldbaa
Starting Member

32 Posts

Posted - 2009-09-22 : 07:10:21

This query fails as in group by we have only name, but in select it is '*'. All in select statment, should be in group by,. But if total is added to group by , then output is grouped by both name and total.

I need the output grouped by name and sum(toatl)>10

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-22 : 07:29:29

Do you mean this?
SELECT
t2.*
from
(
select name,[total] = sum(total)
from [table]
group by name
having sum(total) > 10
) t1
inner join @table t2 on t1.name = t2.name

Jim

Everyday I learn something that somebody else already knew

sqldbaa
Starting Member

32 Posts

Posted - 2009-09-22 : 07:43:26

Thanks for the reply.

I tried with another option using, sub query which also gives the result

select * from [table] where name in ( select name from [table] group by name having sum(total)>10)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources