Help using aggregate functions

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

Help using aggregate functions

Author

Topic

TD
Starting Member

2 Posts

Posted - 2011-01-24 : 22:37:02

I have just imported an access database in sql server (so I am new to sql server). It is a swimming club data base I designed.
One of the reports I generate from the DB is "All Swimmers Best Times for all Strokes, Distances and Pool Length (long or short course)".
The querie below does this

SELECT Entered.SwimmerID, Swimmer.FirstName, Swimmer.Surname, PoolLength.Length, Distance.ID, Distance.Length, Stroke.ID, Stroke.Stroke, Min(Entered.TimeAchieved) AS MinTime

FROM Meet, Eveent, Entered, Swimmer, PoolLength, Distance, Stroke

WHERE Entered.SwimmerID = Swimmer.ID

AND Entered.TimeAchieved > 0
AND (Entered.NoTimeID = 1 OR Entered.NoTimeID = 5)
AND Entered.EventID = Eveent.ID
AND Eveent.MeetID = Meet.ID
AND Meet.PoolLengthID = PoolLength.ID
AND Eveent.DistanceID = Distance.ID
AND Eveent.StrokeID = Stroke.ID
AND Swimmer.Active > 0

GROUP BY Entered.SwimmerID, Swimmer.FirstName, Swimmer.Surname, PoolLength.Length, Distance.ID, Distance.Length, Stroke.ID, Stroke.Stroke

ORDER BY Swimmer.Surname, Swimmer.FirstName, PoolLength.Length, Distance.ID, Stroke.ID

However I need the date that each best time was achieved.
Currently I step through the recordset generated above and execute
the following querie on each individual record.

SELECT DISTINCT Meet.MeetDate

FROM Meet, Entered, Eveent

WHERE Entered.SwimmerID = lngSwimmerID (from above recordset)

AND Entered.TimeAchieved = lngBestTime (from above recordset)
AND Entered.EventID = Eveent.ID
AND Eveent.MeetID = Meet.ID
AND Meet.PoolLengthID = lngPoolLengthID (from above recordset)
AND Eveent.DistanceID = lngDistanceID (from abovr recordset)
AND Eveent.StrokeID = lngStrokeID (from above recordset)

This all works, but I have not been able to incorporate the two
querties into one to make it quicker and more efficient.
I have tried sub queries but my knowledge of such is not as good as I would like.
Any help would be much appreciated.

TD

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-25 : 01:13:49

Check The Below Query

SELECT
Entered.SwimmerID
,Swimmer.FirstName
,Swimmer.Surname
,PoolLength.Length
,Distance.ID
,Distance.Length
,Stroke.ID
,Stroke.Stroke
,Min(Entered.TimeAchieved) AS MinTime
,(SELECT
DISTINCT Meet.MeetDate
FROM Meet M
Inner Join Eveent Ev ON Ev.MeetID = M.ID
Inner Join Entered En ON En.EventID = Ev.ID
WHERE En.SwimmerID = TableName.lngSwimmerID -- replace the tableName with the table Name having lngSwimmerID
AND En.TimeAchieved = TableName.lngBestTime -- replace the tableName with the table Name having lngBestTime
AND M.PoolLengthID = TableName.lngPoolLengthID -- replace the tableName with the table Name having lngPoolLengthID
AND En.DistanceID = TableName.lngDistanceID -- replace the tableName with the table Name having lngDistanceID
AND En.StrokeID = TableName.lngStrokeID) -- replace the tableName with the table Name having lngStrokeID
FROM Meet, Eveent, Entered, Swimmer, PoolLength, Distance, Stroke
WHERE Entered.SwimmerID = Swimmer.ID
AND Entered.TimeAchieved > 0
AND (Entered.NoTimeID = 1 OR Entered.NoTimeID = 5)
AND Entered.EventID = Eveent.ID
AND Eveent.MeetID = Meet.ID
AND Meet.PoolLengthID = PoolLength.ID
AND Eveent.DistanceID = Distance.ID
AND Eveent.StrokeID = Stroke.ID
AND Swimmer.Active > 0
GROUP BY Entered.SwimmerID, Swimmer.FirstName, Swimmer.Surname, PoolLength.Length, Distance.ID, Distance.Length, Stroke.ID, Stroke.Stroke
ORDER BY Swimmer.Surname, Swimmer.FirstName, PoolLength.Length, Distance.ID, Stroke.ID

TD
Starting Member

2 Posts

Posted - 2011-01-25 : 16:10:11

Thanks for getting back to me so quick.
I tried what you suggested. However the recordset produced was that of all swimmers times, not just their fastest. I was able to produce the same recordset (all swimmers times) by just adding Meet.MeetDate to the original SELECT and GROUP BY. This appears to have done the same.

SELECT Entered.SwimmerID, Swimmer.FirstName, Swimmer.Surname, PoolLength.ID, PoolLength.Length, Distance.ID, Distance.Length, Stroke.ID, Stroke.Stroke, Min(Entered.TimeAchieved) AS MinTime ,(SELECT DISTINCT Meet.MeetDate
FROM Meet M
Inner Join Eveent Ev ON Ev.MeetID = M.ID
Inner Join Entered En ON En.EventID = Ev.ID
WHERE En.SwimmerID = Swimmer.ID
AND En.TimeAchieved = Min(Entered.TimeAchieved)
AND M.PoolLengthID = PoolLength.ID
AND Ev.DistanceID = Distance.ID
AND Ev.StrokeID = Stroke.ID)

FROM Meet, Eveent, Entered, Swimmer, PoolLength, Distance, Stroke

WHERE Entered.SwimmerID = Swimmer.ID

AND Entered.TimeAchieved > 0
AND (Entered.NoTimeID = 1 OR Entered.NoTimeID = 5)
AND Entered.EventID = Eveent.ID
AND Eveent.MeetID = Meet.ID
AND Meet.PoolLengthID = PoolLength.ID
AND Eveent.DistanceID = Distance.ID
AND Eveent.StrokeID = Stroke.ID
AND Swimmer.Active > 0

GROUP BY Entered.SwimmerID, Swimmer.FirstName, Swimmer.Surname, PoolLength.ID, PoolLength.Length, Distance.ID, Distance.Length, Stroke.ID, Stroke.Stroke, Swimmer.ID, Meet.MeetDate

ORDER BY Swimmer.Surname, Swimmer.FirstName, PoolLength.ID, Distance.ID, Stroke.ID

I will keep trying but any further suggestions would be very appreciated.
TD

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2011-01-26 : 02:15:24

@"but I have not been able to incorporate the two querties into one to make it quicker and more efficient."

Actually i replied to your query as per above specified issue. Therefore I have just reorganized your queries and put them under one. For any specific help, we would require some more details of the table structures along with sample data and desired output. :)

Cheers!
MIK

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources