mysql查询男女平均分_在一个mysql查询中获得男性/女性/所有人的平均投票评分

我有以下头痛...我试图一次性获得三个不同子句的平均评级，我想也许使用UNION命令可以工作，但它只能从第一个查询中找到值(要求的是averageRating)。 averageMaleRating和averageFemaleRating作为未知列返回...

这是查询！

SELECT

averageRating,

averageMaleRating,

averageFemaleRating

FROM (

SELECT

question_id AS q_id,

ROUND(AVG(rating)) AS averageRating

FROM

wp_ratings

WHERE

club_id = ?

GROUP BY question_id

UNION

SELECT

question_id AS q_id,

ROUND(AVG(rating)) AS averageMaleRating

FROM

wp_ratings

WHERE

club_id = ?

AND

GENDER = 'male'

GROUP BY question_id

UNION

SELECT

question_id AS q_id,

ROUND(AVG(rating)) AS averageFemaleRating

FROM

wp_ratings

WHERE

club_id = ?

AND

GENDER = 'female'

GROUP BY question_id

)AS bigU

JOIN

wp_ratings

ON

wp_ratings.question_id = bigU.q_id