如果在另一列中，则基于总和的Mysql平均值

Question

举个例子，假设我试图计算出每个 parent 对男性和女性的平均分数。

示例数据如下所示:

parentID    childID    sex    score
------------------------------------
1           21         m      17
1           23         f      12
2           33         f      55
2           55         m      22 
3           67         m      26
3           78         f      29
3           93         m      31

这是我想要的结果:

parentID    offspring   m    f   avg-m  avg-f  avg-both
----------------------------------------------------
1           2           1    1   17     12     14.5 
2           2           1    1   22     55     38.5
3           3           2    1   28.5   29     28.67

通过下面的查询，我可以找到男性和女性的平均值，但我不确定如何获得男性或女性的平均值

SELECT parentID, COUNT( childID ) AS offspring, SUM( IF( sex =  'm', 1, 0 ) ) AS m, SUM( IF( sex =  'f', 1, 0 ) ) AS f, max(score) as avg-both
FROM sexb_1
WHERE avg-both > 11
GROUP BY parentID

我在查询中尝试了类似的操作，但它返回了一个错误

AVG(IF(sex = 'm', max(score),0)) as avg-m

Answer 1

I tried something like this in the query but it returns an error

AVG(IF(sex = 'm', max(score),0)) as avg-m

您不能在另一个聚合函数中使用一个聚合函数(在这种情况下，AVG() 中的 MAX())——这意味着什么？一旦发现组的 MAX()，对什么取平均？

相反，您希望在性别符合您要求的情况下采用 AVG() 的 score 值；由于 AVG() 忽略 NULL 值并且不匹配的 CASE 表达式的默认值为 NULL，因此可以简单地执行以下操作:

SELECT   parentID, 
         COUNT(*) offspring,
         SUM(sex='m') m,
         SUM(sex='f') f,
         AVG(CASE sex WHEN 'm' THEN score END) `avg-m`,
         AVG(CASE sex WHEN 'f' THEN score END) `avg-f`,
         AVG(score) `avg-both`
FROM     sexb_1
GROUP BY parentID
HAVING   `avg-both` > 11

查看sqlfiddle .