For example purposes lets say Im trying to figure out the average score for males and females from each parent.
Example data looks like this:
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
This is the result I want:
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
With the below query I can find the average for both males and females but I'm not sure how to get the average for either male or female
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
I tried something like this in the query but it returns an error
AVG(IF(sex = 'm', max(score),0)) as avg-m
I tried something like this in the query but it returns an error
AVG(IF(sex = 'm', max(score),0)) as avg-m
You can't use one aggregate function within another (in this case, MAX() within AVG())—what would that even mean? Once one has discovered the MAX() of the group, over what is there to take an average?
Instead, you want to take the AVG() of score values where the sex matches your requirement; since AVG() ignores NULL values and the default for unmatched CASE expressions is NULL, one can simply do:
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
See it on sqlfiddle.