Get an average vote rating for males/females/all in one mysql query

I have the following headache… I'm trying to get the average rating for three different clauses in one go, I thought maybe using the UNION command would work but it will only find values from the first query (the one asking for averageRating). averageMaleRating and averageFemaleRating are returned as unknown columns…

And here's the query!

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

---

You're using UNION statement totally wrong.

You need to select from Question table and join Ratings table for each gender

SELECT
  q.id,
  AVG(t.rating) AS avgTotal,
  AVG(m.rating) AS avgMale,
  AVG(f.rating) AS avgFemale
FROM questions AS q

LEFT JOIN ratings AS t
ON q.id = t.question_id

LEFT JOIN ratings AS m
ON q.id = m.question_id AND m.gender = 'male'

LEFT JOIN ratings AS f
ON q.id = f.question_id AND f.gender = 'female'

GROUP BY q.id