Mindboggling query for me - 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)

Mindboggling query for me

Author

Topic

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-09-24 : 10:38:44

I have two tables, Users and DoctorVisit

User
-UserID
-Name

DoctorsVisit
-UserID
-Weight
-Date

The doctorVisit table contains all the visits a particular user did to the doctor. The user's weight is recorded per visit.

Query: Sum up all the Users weight, using the last doctor's visit's numbers. (then divide by number of users to get the average weight)

Note: some users may have not visited the doctor at all, while others may have visited many times.

I need the average weight of all users, but using the latest weight.

I want the average weight across all users.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 10:50:43

[code]SELECT SUM(tmp.Weight)*1.0/COUNT(u.UserID) AS Averageweight
FROM Users u
LEFT JOIN(SELECT d.*
FROM DoctorsVisit d
INNER JOIN (SELECT UserID,MAX(Date) AS latest
FROM DoctorsVisit
GROUP BY UserID)d1
ON d1.UserID=d.UserID
AND d1.latest=d.Date)tmp
ON tmp.UserID=u.UserID[/code]

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-24 : 10:52:24

StackOverflow didn't get you the answer you wanted?

a bit homeworkish isn't it?

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

sql777
Constraint Violating Yak Guru

314 Posts

Posted - 2008-09-24 : 10:59:13

haha..man its NOT! (I did rename the tables, but Im at work so lay off! haha)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-09-24 : 11:04:02

_______________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.0 out!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources