mysql - 如何按名称获取大于和小于平均值的计数

Question

我有一个包含名称和他们的交易的数据集，如何获得平均值和交易数量大于该平均值和小于该平均值..

Name    Transaction
John    12
John    34
John    45
John    66
John    32
chris   26
chris   54
chris   56
chris   99
chris   13
chris   4
kim     22
kim     34
kim     7
kim     11
kim     34

O/P 将是

Name      Avg     Count_greater_than_Avg    Count_Less_than_Avg
John      37.8          2                           3
chris     42            3                           3
kim       21.6          3                           2

提前致谢..

Answer 1

试试这个:

SELECT t1.Name, t2.Aver,
       COUNT(CASE WHEN Transaction < Aver THEN 1 END) Count_Less_than_Avg,
       COUNT(CASE WHEN Transaction > Aver THEN 1 END) Count_greater_than_Avg
FROM mytable AS t1
JOIN (
  SELECT Name, AVG(Transaction * 1.0) AS Aver
  FROM mytable
  GROUP BY Name
) AS t2 ON t1.Name = t2.Name
GROUP By Name

您需要一个派生表来计算每个 Name 的平均值。然后您可以JOIN原始表到这个派生表并使用条件聚合来计算小于、大于 em> 交易数量。

Demo here