Sum function - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Sum function

Author

Topic

konark
Yak Posting Veteran

60 Posts

Posted - 2009-09-02 : 17:50:56

Section Name rollNo marks
A TOM 1 90
A HARRY 2 80
A JOHN 3 80
A TOM 4 90
B CHRIS 5 90
B CHRIS 6 80

select Section , Name, RollNo,
sum(marks) , -- For distinct values of Name
sum(marks) -- For distinct values of Name and Section

from table

Can anyone help ?

Chandragupta Mourya

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 18:20:45

That would typically be done with two different SELECT statements. Post the output as you would like to see it.

Be One with the Optimizer
TG

singularity
Posting Yak Master

153 Posts

Posted - 2009-09-02 : 19:36:11

Maybe this:


select distinct Section, Name, RollNo,
sum(marks) over  (partition by name), 
sum(marks) over (partition by name, section)

from table

konark
Yak Posting Veteran

60 Posts

Posted - 2009-09-02 : 19:41:54

Sec Name Rollno Marks Sum(marks) Sum(Marks)
A TOM 1 90 340 180
A HARRY 2 80 340 80
A JOHN 3 80 340 80
A TOM 4 90 340 180
B CHRIS 5 90 170 170

B CHRIS 6 80 170 170

Chandragupta Mourya

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-09-02 : 20:25:36

Singularity has it - just wrong column in the partition by:


declare @t table (Section char(1), Name varchar(10), rollNo int,  marks int)
insert @t
select 'A', 	'TOM', 1, 90 union all
select 'A', 	'HARRY', 2, 80 union all
select 'A', 	'JOHN', 3, 80 union all
select 'A', 	'TOM', 4, 90 union all
select 'B', 	'CHRIS', 5, 90 union all
select 'B', 	'CHRIS', 6, 80


select Section
       ,Name
       ,RollNo
       ,sum(marks) over  (partition by section) as [sum(marks)1]
       ,sum(marks) over (partition by section, name) [sum(marks)2]
from @t
order by rollNo

output:
Section Name       RollNo      sum(marks)1 sum(marks)2
------- ---------- ----------- ----------- -----------
A       TOM        1           340         180
A       HARRY      2           340         80
A       JOHN       3           340         80
A       TOM        4           340         180
B       CHRIS      5           170         170
B       CHRIS      6           170         170

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources