Sum of top n rows
-
studentID subjectIDsubjectMark
1192
1288
1388
1483
1583
1674
2178
2289
2365
2477
2590
2681
Trying to find sum of top 4 marks out of 6 subjects by student.
-
Its the same way as SQLServer brings out top 4 rows, deciding internally on the tie breaker.
SELECT TOP 4 subjectMark
FROM Table_1
WHERE (studentid = 1)
I would like to have sum this 4 marks in a single SQL and not by putting these marks in a temp table and summing them.
-
arijit (6/4/2009)
I would like to have sum this 4 marks in a single SQL and not by putting these marks in a temp table and summing them.Hi,
Try this
create table #temp
(
studentID int,
subjectID int,
subjectMark int
)
insert into #temp
select 1, 1, 92
union all
select 1, 2, 88
union all
select 1, 3, 88
union all
select 1, 4, 83
union all
select 1, 5, 83
union all
select 1, 6, 74
union all
select 2, 1, 78
union all
select 2, 2, 89
union all
select 2, 3, 65
union all
select 2, 4, 77
union all
select 2, 5, 90
union all
select 2, 6, 81
--select * from #temp
select a.studentID,sum(subjectMark)TOP_SUMfrom #temp a
where a.subjectID in( select top 4 subjectID from #temp
where studentID = a.studentID
order by subjectMark desc)
group by a.studentID
RESULT
studentID|TOP_SUM
1|351
2|338
ARUN SAS
-
Can't remember if this works in SQL 2000 or not:
SELECT
StudentID,
SubjectID,
(Select SUM(subjectMark)
FROM
(
Select TOP 4
StudentID, SubjectID, subjectMark
From YourTable as t
Where t.studentID = g.studentID
And t.subjectid = g.subjectid
ORDER BY subjectMark
) S
) AS g
FROM
(
Select Distinct
StudentID, SubjectID
From YourTable
) AS g
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Thank you Arun, Your solution works just perfect.
-
Hi,
I don’t know why you need the top 4 maximum score; the real situation may be the sum of the score grate than 50 or 60, however you got the coding that fine to us!!
ARUN SAS
-
Barry, your code has some issue and does not run in SQ 2000.
The error given is
Invalid column name 'studentID'.
create table #temp
(
studentID int,
subjectID int,
subjectMark int
)
insert into #temp
select 1, 1, 92
union all
select 1, 2, 88
union all
select 1, 3, 88
union all
select 1, 4, 83
union all
select 1, 5, 83
union all
select 1, 6, 74
union all
select 2, 1, 78
union all
select 2, 2, 89
union all
select 2, 3, 65
union all
select 2, 4, 77
union all
select 2, 5, 90
union all
select 2, 6, 81
--select * from #temp
SELECT
StudentID,
SubjectID,
(Select SUM(subjectMark)
FROM
(
Select TOP 4
StudentID, SubjectID, subjectMark
From #temp as t
Where t.studentID = g.studentID
And t.subjectid = g.subjectid
ORDER BY subjectMark
) S
) AS g
FROM
(
Select Distinct
StudentID, SubjectID
From #temp
) AS g
-
Sorry, it works fine for me?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
Barry, I am doing this in SQL 2000, don't have 2005/08
Using you code and changing the table name from yourtable to #temp,
and using #temp table creation, I am getting this as I run the entire code in SQL Query Analyzer
Server: Msg 207, Level 16, State 3, Line 32
Invalid column name 'studentID'.
Server: Msg 207, Level 16, State 1, Line 32
Invalid column name 'subjectid'.
I am running the code exactly as below:
create table #temp(
studentID int,
subjectID int,
subjectMark int
)
insert into #temp
select 1, 1, 92
union all
select 1, 2, 88
union all
select 1, 3, 88
union all
select 1, 4, 83
union all
select 1, 5, 83
union all
select 1, 6, 74
union all
select 2, 1, 78
union all
select 2, 2, 89
union all
select 2, 3, 65
union all
select 2, 4, 77
union all
select 2, 5, 90
union all
select 2, 6, 81
--select * from #temp
SELECT
StudentID,
SubjectID,
(Select SUM(subjectMark)
FROM
(
Select TOP 4
StudentID, SubjectID, subjectMark
From #temp as t
Where t.studentID = g.studentID
And t.subjectid = g.subjectid
ORDER BY subjectMark
) S
) AS g
FROM
(
Select Distinct
StudentID, SubjectID
From #temp
) AS g
-
The code works fine on SQL 2000, but could it be that your server is using a case sensistive collation?
I noticed that the columnnames somteines use capitol letters and sometimes they don't.
[font="Verdana"]Markus Bohse[/font]
-
Ooh, good point, Marcus.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font] -
arijit (6/4/2009)
Barry, your code has some issue and does not run in SQ 2000.The error given is
Invalid column name 'studentID'.
You probably have a left over #Temp table in the current connection. Or, it could be the case sensitivity thing the others spoke of.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Sorry doesn't work yet here. Two possibilities were brought up by Marcus - "case sensistive collation? " and Jeff - "left over #Temp table in the current connection"
To be sure on first, I took the code and changed everything to lowercase. And for second I am trying this after 2 days and even in the first time the error came up.
I am using the code exactly as below and it throws up error as :
Server: Msg 207, Level 16, State 3, Line 20
Invalid column name 'studentid'.
Server: Msg 207, Level 16, State 1, Line 20
Invalid column name 'subjectid'.
create table #temp(
studentid int, subjectid int, subjectmark int
)
insert into #temp
select 1, 1, 92 union all
select 1, 2, 88 union all
select 1, 3, 88 union all
select 1, 4, 83 union all
select 1, 5, 83 union all
select 1, 6, 74 union all
select 2, 1, 78 union all
select 2, 2, 89 union all
select 2, 3, 65 union all
select 2, 4, 77 union all
select 2, 5, 90 union all
select 2, 6, 81
--select * from #temp
select studentid, subjectid, --this is line 20
(select sum(subjectmark)
from
(
select top 4 studentid, subjectid, subjectmark
from #temp as t where t.studentid = g.studentid
and t.subjectid = g.subjectid order by subjectmark
) s
) as g
from
(
select distinct studentid, subjectid from #temp
) as g
Enclosing attachment of the screenshot with code + error. I have reformatted the code a bit to be able to show in same screen.
-
Amazing... what the hell did the boys in Redmond do? The code posted above by arijit works just dandy in 2k5. The error appears when you try it in 2k and it appears just like what arijit shows.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply