Compute Users average weight

Question

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.

Update

I want the average weight across all users.

Answer 1

Please clarify:

Do you want "user's average weight" or "users' average weight" where:

• "user's average weight" is each user's average weight across all of their visits.
• "users' average weight" is the average weight across all users as measured on their last visit (sounds like this one, but I want to be sure)

Answer 2

This should get you the average weight per user if they have visited:

select user.name, temp.AvgWeight
from user left outer join (select userid, avg(weight)
      from doctorsvisit
      group by userid) temp
    on user.userid = temp.userid

Answer 3

Write a query to select the most recent weight for each user (QueryA), and use that query as an inner select of a query to select the average (QueryB), e.g.,

SELECT AVG(weight) FROM (QueryA)

Answer 4

If I understand your question correctly, you should be able to get the average weight of all users based on their last visit from the following SQL statement. We use a subquery to get the last visit as a filter.

SELECT avg(uv.weight) FROM (SELECT weight FROM uservisit uv INNER JOIN
(SELECT userid, MAX(dateVisited) DateVisited FROM uservisit GROUP BY userid) us 
ON us.UserID = uv.UserId and us.DateVisited = uv.DateVisited

I should point out that this does assume that there is a unique UserID that can be used to determine uniqueness. Also, if the DateVisited doesn't include a time but just a date, one patient who visits twice on the same day could skew the data.

Answer 5

I think there's a mistake in your specs.

If you divide by all the users, your average will be too low. Each user that has no doctor visits will tend to drag the average towards zero. I don't believe that's what you want.

I'm too lazy to come up with an actual query, but it's going to be one of these things where you use a self join between the base table and a query with a group by that pulls out all the relevant Id, Visit Date pairs from the base table. The only thing you need the User table for is the Name.

We had a sample of the same problem in here a couple of weeks ago, I think. By the "same problem", I mean the problem where we want an attribute of the representative of a group, but where the attribute we want isn't included in the group by clause.

Answer 6

I think this will work, though I could be wrong:

Use an inner select to make sure you have the most recent visit, then use AVG. Your User table in this example is superfluous: since you have no weight data there and you don't care about user names, it doesn't do you any good to examine it.

SELECT AVG(dv.Weight) 
FROM DoctorsVisit dv
WHERE dv.Date = (
      SELECT MAX(Date)
        FROM DoctorsVisit innerdv
       WHERE innerdv.UserID = dv.UserID
  )

Answer 7

If you're using SQL Server 2005 you don't need the sub query on the GROUP BY.
You can use the new ROW_NUMBER and PARTION BY functionality.

SELECT AVG(a.weight)  FROM
(select
    ROW_NUMBER() OVER(PARTITION BY dv.UserId ORDER BY Date desc) as ID,
    dv.weight     
from 
    DoctorsVisit dv) a   
WHERE a.Id = 1

As someone else has mentioned though, this is the average weight across all the users who have VISITED the doctor. If you want the average weight across ALL of the users then anyone not visiting the doctor will give a misleading average.

Answer 8

Here's my stab at the solution:

select
    avg(a.Weight) as AverageWeight
from
    DoctorsVisit as a
innner join
    (select 
        UserID,
        max (Date) as LatestDate
     from
        DoctorsVisit
     group by
        UserID) as b
     on a.UserID = b.UserID and a.Date = b.LatestDate;

Note that the User table isn't used at all.

This average omits entirely users who have no doctors visits at all, or whose weight is recorded as NULL in their latest doctors visit. This average is skewed if any users have more than one visit on the same date, and if the latest date is one of those date where the user got wighed more than once.