MySQL using calculated return values to calculate other values

Question

This may be difficult to explain, so bare with me.

Let's say I have a select Statement with the following:

SUM(st.tafPoints/200) as totalFriendReferrals,
SUM(CASE WHEN st.gender = "m" THEN st.tafPoints/200 ELSE 0 END) as maleFriendReferrals,
SUM(CASE WHEN st.gender = "f" THEN st.tafPoints/200 ELSE 0 END) as femaleFriendReferrals

I need to calculate the percentage of maleFriendReferrals based on the totalFriendReferrals. These are both derived (ie, not originally in the list of columns) values. So if I try to do something like:

CONCAT( (maleFriendReferrals/totalFriendReferrals) *100 , '%') as malePercentReferrals

But I get an error:

Unknown column 'maleFriendReferrals' in 'field list' – 1 ms

Is there a way to do this easily? I need to be able to do this within the query itself, and not have to loop through my results to calculate percentages.

Answer 1

You should put the original query to a subquery

SELECT CONCAT( (maleFriendReferrals/totalFriendReferrals) *100 , '%') as malePercentReferrals
FROM
  (SELECT
    SUM(st.tafPoints/200) as totalFriendReferrals,
    SUM(CASE WHEN st.gender = "m" THEN st.tafPoints/200 ELSE 0 END) as maleFriendReferrals,
    SUM(CASE WHEN st.gender = "f" THEN st.tafPoints/200 ELSE 0 END) as femaleFriendReferrals
  FROM st) AS subquery