SQL:计数值高于组的平均值 [英] SQL: Count values higher than average for a group

问题描述

如何使用SQL对高于组平均值的值进行计数?

How can one use SQL to count values higher than a group average?

例如:

我的表A具有:

q   t
1   5
1   6
1   2
1   8
2   6
2   4
2   3   
2   1

第1组的平均值为5.25.该组中有两个高于5.25的值，分别是8和6.因此该组中高于平均值的值计数为2.

The average for group 1 is 5.25. There are two values higher than 5.25 in the group, 8 and 6; so the count of values that are higher than average for the group is 2.

第2组的平均值为3.5.该组中有两个高于3.5的值:5和6.因此该组中高于平均值的值计数为2.

The average for group 2 is 3.5. There are two values higher than 3.5 in the group, 5 and 6; so the count of values that are higher than average for the group is 2.

推荐答案

尝试一下:

 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 the subquery you will count average value for both groups, join it on your table, and then you will select count of all values from your table where the value is bigger then average

这篇关于SQL:计数值高于组的平均值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！