CODEWITHSUNDEEP
sqlsql-server-2008
Alex P
Alex P

Reputation: 12487

SQL - Select unique row with average of all other rows

I am struggling with a SQL query that I initially thought should be quite simple.

Imagine a table Users that uses a UserID as the PK and a column Age for the users age:

    UserID     Age
    1          22
    2          34
    3          23
    4          19
    etc.

I'd like to be able to specify a UserID and return that user's age as well as the average age of all other users. For example, if I specify UserID 1 then I'd like to see return set as:

   UserID      Age      AvgAge
   1           22       24.5

The following does not work: (as the WHERE is performed before GROUP BY)

  Select UserID, Age, Avg(Age) as 'AvgAge'
  From Users
  Where UserID = 1
  Group By UserId, Age

  UserID      Age      AvgAge    //Result set
  1           22       22

Can anybody nudge me in the right direction?

By the way, in an ideal world the average age should not include the user that has been specified as the idea is to show their age relative to the average age of everybody else.

Given that there are 1000+ users then taking an average over all users will make no practical difference to the AvgAge number, though if anybody would like to show off their SQL prowess with a solution to that then I'd be interested to see it.

Thanks

Upvotes: 0

Views: 416

Answers (6)

t-clausen.dk
t-clausen.dk

Reputation: 44326

declare @T table (UserID int, Age int)  
insert into @T values(1,22),(2,34),(3,23),(4,19)  

declare @UserID int = 1 

;with a as
(
  select userid, Age, 
  avg(age * case when userid <> @userid then 1.0 end) over() 'AvgAge'
from @T             
)
select Age, AvgAge from a
where userid = @UserID

Upvotes: 1

alexsuslin
alexsuslin

Reputation: 4225

SELECT 
    u.UserId, 
    u.Age, 
    b.AvgAge 
FROM 
    dbo.Users a, 
    (SELECT AVG(Age*1e0) as AvgAge FROM dbo.Users) as b

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138970

declare @T table
(
  UserID int,
  Age int
)

insert into @T values
    (1,          22),
    (2,          34),
    (3,          23),
    (4,          19)

declare @UserID int = 1

select Age, (select avg(Age*1.0)
             from @T
             where UserID <> @UserID) as AvgAge
from @T
where UserID = @UserID

Result:

Age         AvgAge
----------- ---------------------------------------
22          25.333333

Upvotes: 3

Matt Gibson
Matt Gibson

Reputation: 14959

This query excludes the user with the specified id from the average, as requested. You used MAX in your example, which is not going to give you the average, but if MAX is actually what you want, you can just swap it with AVG in this query and it'll work.

 SELECT u.UserID, 
        u.Age,
        (SELECT AVG(uavg.Age)
           FROM Users uavg
          WHERE uavg.UserID != u.UserID) AS AvgAge
    FROM Users u
   WHERE u.UserID = 1

Upvotes: 2

Sparky
Sparky

Reputation: 15095

  Select U.UserID, u.Age, sq.Age as 'AvgAge'
      From Users u
      join (select average(age) as Age from users) sq on 1=1
      Where UserID = 1
      Group By UserId, Age

Upvotes: 1

Vikram
Vikram

Reputation: 8333

use what you need avg for average and max for maximum age:

Select 
 UserID, 
 Age, 
 (select Max(Age) from Users) as 'AvgAge'
  From Users
  Where UserID = 1

Upvotes: 1

Related Questions