Group by problem - 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

Group by problem

Author

Topic

Dipu710646
Starting Member

10 Posts

Posted - 2010-11-23 : 00:53:27

I have a table like this :-

class student sub marks
c1 st1 s1 30
c1 st1 s2 30
c1 st1 s3 40
c1 st2 s1 40
c1 st2 s2 20
c1 st2 s3 20
c2 st1 s1 20
c2 st1 s2 20
c2 st1 s3 50
c2 st2 s1 50
c2 st2 s2 50
c2 st2 s3 30
c1 st3 s1 30
c1 st3 s2 40
c1 st3 s3 70
c2 st3 s1 70
c2 st3 s2 70
c2 st3 s3 70

I want class wise topper i.e who got maximum total number in a class.

How will be the query.

Please help me.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-11-23 : 01:00:57

what is sub?

Dipu710646
Starting Member

10 Posts

Posted - 2010-11-23 : 01:01:36

it is subject...

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-11-23 : 01:08:54

what have you tried till now?

Dipu710646
Starting Member

10 Posts

Posted - 2010-11-23 : 01:53:52

select class,stud,sum(marks) from classMarks group by class,stud having sum(marks)>90

It is giving out put like..

c1 st1 100
c2 st2 130
c1 st3 140
c2 st3 210

I want class wise topper..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-23 : 02:11:20

Show your wanted output in relation to the posted testdata.

No, you're never too old to Yak'n'Roll if you're too young to die.

Dipu710646
Starting Member

10 Posts

Posted - 2010-11-23 : 03:08:38

no man..I want like

c1 st3 140
c2 st3 210..i.e class wise topper..

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-23 : 03:37:12

[code]declare @sample table (class varchar(10), student varchar(10), marks int)
insert @sample
select 'c1', 'st1', 30 union all
select 'c1', 'st1', 30 union all
select 'c1', 'st1', 40 union all
select 'c1', 'st2', 40 union all
select 'c1', 'st2', 20 union all
select 'c1', 'st2', 20 union all
select 'c2', 'st1', 20 union all
select 'c2', 'st1', 20 union all
select 'c2', 'st1', 50 union all
select 'c2', 'st2', 50 union all
select 'c2', 'st2', 50 union all
select 'c2', 'st2', 30 union all
select 'c1', 'st3', 30 union all
select 'c1', 'st3', 40 union all
select 'c1', 'st3', 70 union all
select 'c2', 'st3', 70 union all
select 'c2', 'st3', 70 union all
select 'c2', 'st3', 70

select class,student,sum_marks
from
(
select
row_number() over (partition by class order by sum(marks) desc) as rownum,
class,student,sum(marks) as sum_marks from @sample group by class,student having sum(marks)>90
)dt
where rownum=1
[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

sreekanth939
Starting Member

12 Posts

Posted - 2010-11-23 : 03:39:25

hi,

try this query

"In"

, "subquery"

CREATE TABLE [dbo].[abc](
[class] [varchar](10) NULL,
[STudent] [varchar](10) NULL,
[subject] [varchar](10) NULL,
[mark] [decimal](18, 2) NULL
)

select student,class,sum(mark) from abc as F
group by student,class
having sum(mark) in ( select max(B) from( select sum(mark) as B from abc where class=F.class group by student,class ) as v)

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-23 : 03:40:16

maybe

with cte as
(
select class, student, marks = sum(marks)
from tbl
group by student
)
select cte.*
from cte
join (select class, max(marks) from cte) b
on cte.class = b.class
and cte.marks = b.marks

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Dipu710646
Starting Member

10 Posts

Posted - 2010-11-23 : 04:06:57

Thanks buddies..

select student,class,sum(mark) from abc as F
group by student,class
having sum(mark) in ( select max(B) from( select sum(mark) as B from abc where class=F.class group by student,class ) as v)

this works..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources