SQL: values above the group average
How do I use SQL to count values above the group average?
For example:
I have a table A
with:
q t
1 5
1 6
1 2
1 8
2 6
2 4
2 3
2 1
The average for group 1 is 5.25. The group has two values above 5.25, 8, and 6; therefore, the number of values above the group average is 2.
The average for group 2 is 3.5. There are two values in the group above 3.5, 5 and 6; therefore, the number of values above the group average is 2.
source to share
Try the following:
select count (*) as countHigher,a.q from yourtable a join
(select AVG(t) as AvgT,q from yourtable a group by q) b on a.q=b.q
where a.t > b.AvgT
group by a.q
In a subquery, you will calculate the average for both groups, join it to your table, and then select the number of all values from your table where the value is greater than the average
source to share
My answer is very similar to the other answers, except that the average is calculated with decimal values by adding multiplication with 1.0
.
This does not adversely affect the values, but it does an implicit integer-to-floating-point conversion so that the comparison is done using 5.25
for the first group, 3.5
for the second group instead of 5
and 4
respectively.
SELECT count(test.q) GroupCount
,test.q Q
FROM test
INNER JOIN (
SELECT q
,avg(t * 1.0) averageValue
FROM test
GROUP BY q
) result ON test.q = result.q
WHERE test.t > result.averageValue
GROUP BY test.q
Here is a working SQLFiddle of this code .
This query should work with the most common RDBMS systems (SQL Server, Oracle, Postgres).
source to share
You can use AVG(t) OVER(PARTITION BY)
as follows. SQL Fiddle
Query
SELECT COUNT(*)OVER(PARTITION BY q) overcount,q,t
FROM
(
SELECT q,t,AVG(t)OVER(PARTITION BY q) grp_avg FROM o
)C
WHERE t > grp_avg;
Output
| overcount | q | t |
|-----------|---|---|
| 2 | 1 | 6 |
| 2 | 1 | 8 |
| 2 | 2 | 6 |
| 2 | 2 | 4 |
source to share