Marc Rasmussen Marc Rasmussen - 10 days ago 5
MySQL Question

Mysql using an "as" for more calculation

Say i have the following

SQL
statment:

SELECT
COUNT(UHAM.module_id) AS total,
COUNT(CASE WHEN UHAM.is_complete = 1
THEN 1
ELSE NULL END) AS complete,
(total / complete * 100) AS percentage,
AT.name
FROM user_has_academy_module UHAM
JOIN academy_team AT ON AT.id = UHAM.academy_team_id
WHERE academy_team_id IN (317, 305)
GROUP BY UHAM.academy_team_id;


As you can see ive used
total
and
complete
to try and calculate a new value. However this cannot be done as my script says:
unknown column total


So my question is: is there a way to do the above or do i need to write the same
count
statement again (making the sql kinda messy)

Answer

Wrap it with another select :

SELECT t.*,
        (total / complete * 100) AS percentage
FROM(
    SELECT
      COUNT(UHAM.module_id)    AS total,
      COUNT(CASE WHEN UHAM.is_complete = 1
        THEN 1
            ELSE NULL END)     AS complete,
      AT.name
    FROM user_has_academy_module UHAM
      JOIN academy_team AT ON AT.id = UHAM.academy_team_id
    WHERE academy_team_id IN (317, 305)
    GROUP BY UHAM.academy_team_id) t

You can't use an alias on the same level it was created, you either have to copy the whole expression again, or wrap it with another select like the example above.