selecting sum of top 5 - 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)

selecting sum of top 5

Author

Topic

djnibler
Starting Member

3 Posts

Posted - 2010-02-27 : 14:45:00

Can't figure this one out. Looking for the most simple solution.

create table scores (
UserName varchar(10),
NumPoints int,
compDate DateTime
)

insert into scores values ('John',2,'1/1/2010')
insert into scores values ('John',4,'1/1/2010')
insert into scores values ('John',1,'1/1/2010')
insert into scores values ('John',5,'1/1/2010')
insert into scores values ('John',8,'1/1/2010')
insert into scores values ('John',10,'1/1/2010')
insert into scores values ('James',8,'1/1/2010')
insert into scores values ('James',3,'1/1/2010')
insert into scores values ('James',1,'1/1/2010')
insert into scores values ('James',7,'1/1/2010')
insert into scores values ('James',12,'1/1/2010')
insert into scores values ('James',5,'1/1/2010')

I want to select the username, and sum of the top 5 scores for each username, ordered by sum of the score

The query would return:
James, 35
John, 29

Sachin.Nand

2937 Posts

Posted - 2010-02-27 : 14:52:03

[code]

select username,sum(numpoints)from
(
select *,ROW_NUMBER()over(partition by Username order by compdate)as rowid from scores
) t where rowid<=5 group by UserName order by SUM(numpoints)

[/code]

PBUH

djnibler
Starting Member

3 Posts

Posted - 2010-02-27 : 16:01:41

Thanks for your help, I tried to apply that to the schema I have and I am not good enough to figure it out. To try and make my question easier to answer, my previous post was actually a sample of a derived table I was creating, but I am not good enough with SQL to figure out how to apply your example to my derived table so I'm going to give you the whole thing here:

CREATE TABLE [dbo].[Weekend_Flights] (
[rowid] [int] IDENTITY (1, 1) NOT NULL ,
[MemberNumber] [varchar] (10) ,
[FlightDate] [DateTime] ,
[fHours] [int] NOT NULL ,
[fMinutes] [int] NOT NULL ,
[fSeconds] [int] NOT NULL ,
[DistanceFromSpot] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Members] (
[rowid] [int] IDENTITY (1, 1) NOT NULL ,
[MemberNumber] [varchar] (10) ,
[FName] [varchar] (20) ,
[LName] [varchar] (20) ,
) ON [PRIMARY]
GO

INSERT INTO MEMBERS (MemberNumber,FName,LName) VALUES (1,'John','Smith')
INSERT INTO MEMBERS (MemberNumber,FName,LName) VALUES (2,'Jane','Doe')

INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/1/2010',2,3,55,33)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/1/2010',0,3,10,20)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/1/2010',5,2,55,18)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/1/2010',1,1,23,7)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/5/2010',4,3,11,5)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/5/2010',5,5,20,3)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/7/2010',1,1,12,19)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/10/2010',8,3,21,10)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/11/2010',9,9,11,1)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/11/2010',1,2,14,2)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/11/2010',2,3,55,33)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/15/2010',0,3,10,20)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/15/2010',5,2,55,18)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/15/2010',1,1,23,7)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/15/2010',4,3,11,5)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/16/2010',5,5,20,3)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/17/2010',1,1,12,19)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/17/2010',8,3,21,10)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/17/2010',9,9,11,1)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (1,'1/17/2010',1,2,14,2)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/1/2010',2,3,55,33)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/1/2010',0,3,10,20)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/1/2010',5,2,55,18)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/1/2010',1,1,23,7)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/5/2010',4,3,11,5)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/5/2010',5,5,20,3)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/7/2010',1,1,12,19)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/10/2010',8,3,21,10)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/11/2010',9,9,11,1)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/11/2010',1,2,14,2)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/11/2010',2,3,55,33)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/15/2010',0,3,10,20)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/15/2010',5,2,55,18)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/15/2010',1,1,23,7)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/15/2010',4,3,11,5)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/16/2010',5,5,20,3)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/17/2010',1,1,12,19)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/18/2010',8,3,22,10)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/19/2010',9,9,11,3)
INSERT INTO Weekend_Flights (MemberNumber,FlightDate,fHours,fMinutes,fSeconds,DistanceFromSpot) VALUES (2,'1/19/2010',1,2,14,2)

The table and inserts I gave you previously were for a derived table based on this data. The query was:
SELECT FName, LName, COUNT(Weekend_Flights.MemberNumber) AS TotFlights, FlightDate FROM Weekend_Flights INNER JOIN Members ON Weekend_Flights.MemberNumber = Members.MemberNumber GROUP BY FName, LName, FlightDate ORDER BY LName, COUNT(Weekend_Flights.MemberNumber)
That returned the number of rows(aka flights) that each pilot had for each day.

What I need is the same thing: For each pilot, the sum of the number of flights (rows) for each pilots best 5 days (where best means most number of flights {aka rows} on a given day). Order by num flights DESC
Output would be:
John, Smith, 17
Jane, Doe, 15

The second query I need is: For each pilot, the sum of time (in seconds) for each pilot's best 5 days (where best means the total number of fHours+fMinutes+fSeconds for a given day is highest) order by total seconds DESC
Output would look like (one row per pilot):
John, Smith, 212538
Jane, Doe, 143080

The third query I need is: For each pilot, the AVERAGE of (X) for each pilot's best 5 days (where best day is determined by first finding the Max(100 - DistanceFromSpot) for each pilot for each day (which becomes X), then selecting the top 5 days from that list) order by score DESC
Note: It is possible that a pilot won't have 5 days of scores so when calculating the average, this would have to be taken into account.
Output would look like (one row per pilot):
John, Smith, 97.4
Jane, Doe, 97.2

I hope my math is accurate on the results examples above... had to do a lot of adding, may have made an error or two.

I need several more queries but I think if I get these three, I will be able to figure out the rest on my own because they are fairly similar (I hope)!

Thanks again for your help!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-27 : 23:53:49


SELECT Name,SUM(NoOfFlights) AS NoOfFlights
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY NoOfFlights DESC) AS Seq
FROM
(
SELECT COALESCE(m.FName+ ' ','') + m.LName AS Name,DATEADD(day,DATEDIFF(day,0,wf.FlightDate),0) AS [Date],COUNT(*) AS NoOfFlights 
FROM Members m
JOIN Weekend_Flights wf
ON wf.MemberNumber=m.MemberNumber
GROUP BY  COALESCE(m.FName+ ' ','') + m.LName,DATEADD(day,DATEDIFF(day,0,wf.FlightDate),0)
)t
)r
WHERE Seq<=5
GROUP BY Name

output
-------------------------
Name	      NoOfFlights
Jane Doe	15
John Smith	17


SELECT Name,SUM(PilotTime) AS PilotTime
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY PilotTime DESC) AS Seq
FROM
(
SELECT COALESCE(m.FName+ ' ','') + m.LName AS Name,DATEADD(day,DATEDIFF(day,0,wf.FlightDate),0) AS [Date],SUM((60*60*fHours)+(60*fMinutes)+fSeconds) AS PilotTime
FROM Members m
JOIN Weekend_Flights wf
ON wf.MemberNumber=m.MemberNumber
GROUP BY  COALESCE(m.FName+ ' ','') + m.LName,DATEADD(day,DATEDIFF(day,0,wf.FlightDate),0)
)t
)r
WHERE Seq<=5
GROUP BY Name

output
----------------------------------
Name	       PilotTime
Jane Doe	179838
John Smith	212511


SELECT Name,AVG(maxdist*1.0) AS maxdist
FROM
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Name ORDER BY maxdist DESC) AS Seq
FROM
(
SELECT COALESCE(m.FName+ ' ','') + m.LName AS Name,DATEADD(day,DATEDIFF(day,0,wf.FlightDate),0) AS [Date],MAX(100-DistanceFromSpot) AS maxdist
FROM Members m
JOIN Weekend_Flights wf
ON wf.MemberNumber=m.MemberNumber
GROUP BY  COALESCE(m.FName+ ' ','') + m.LName,DATEADD(day,DATEDIFF(day,0,wf.FlightDate),0)
)t
)r
WHERE Seq<=5
GROUP BY Name

output
-------------------------------
Name	        maxdist
Jane Doe	97.200000
John Smith	97.400000