CODEWITHSUNDEEP
sqlsql-serversql-server-2012average
user3908206
user3908206

Reputation: 45

Average without grouping or subquery

I realize this is an odd question, but I'd like to know if this is possible.

Let's say I have a DB with ages and IDs. I need to compare each ID's age to the average age, but I can't figure out how to do that without grouping or subqueries.

SELECT
ID,
AGE - AVG(AGE)
FROM
TABLE

I'm trying to get something like the above, but obviously that doesn't work because ID isn't grouped, but I group, then it calculates the average for each group, and not the table as a whole. How can I get a global average without a subquery?

Upvotes: 1

Views: 1560

Answers (3)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

 SELECT ID,
        AGE -
        AVG(AGE) OVER (partition by ID) as age_2
 FROM Table

I just read is global avg

 SELECT ID,
        AGE -
        AVG(AGE) OVER () as age_2
 FROM Table

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269533

The window logic for average age is:

SELECT ID, AGE - ( AVG(AGE) OVER () )
FROM TABLE;

You do not want ORDER BY in the partitioning clause.

Upvotes: 1

Morpheus
Morpheus

Reputation: 1634

How can I get a global average without a subquery?

Use a variable.

DECLARE @AvgAge decimal(4,3)
SELECT @AvgAge = AVG(AGE) FROM TABLE
SELECT ID, AGE - @AvgAge [AgeDiff] FROM TABLE

Upvotes: 0

Related Questions