MYSQL: Using math in SELECT with alias-CodePudding

I have an extremely complex SQL query that I am needing help with. Essentially, this query will see how many total assignments a student is assigned (total) and how many they have completed (completed) for the course. I need one final column that would give me the percentage of completed assignments, because I want to run a query to select all users who have completed less than 50% of their assignments.

What am I doing wrong? I am getting an error "Unknown column 'completed' in 'field list'"
Is there a better way to execute this? I am open to changing my query.

Query:

SELECT students.usid AS ID, students.firstName, students.lastName, 
(
    SELECT COUNT(workID) FROM assignGrades
    INNER JOIN students ON students.usid = assignGrades.usid
    INNER JOIN assignments ON assignments.assID = assignGrades.assID
    WHERE 
        assignGrades.usid = ID AND 
        assignments.subID = 4 AND
        (
            assignGrades.submitted IS NOT NULL OR 
            (assignGrades.score IS NOT NULL AND CASE WHEN assignments.points > 0 THEN assignGrades.score ELSE 1 END > 0)
        )
) AS completed,
(
    SELECT COUNT(workID) FROM assignGrades
    INNER JOIN students ON students.usid = assignGrades.usid
    INNER JOIN assignments ON assignments.assID = assignGrades.assID
    WHERE 
        assignGrades.usid = ID AND 
        assignments.subID = 4 AND
        (NOW() - INTERVAL 5 HOUR) > assignments.assigned
) AS total, 
(completed/total)*100 AS percentage
FROM students 
INNER JOIN profiles ON profiles.usid = students.usid 
INNER JOIN classes ON classes.ucid = profiles.ucid 
WHERE classes.utid=2 AND percentage < 50

If I cut the (percentage) part in the SELECT statement, the query runs as expected. See below for results.

Information about the tables involved in this query:

assignGrades: Lists the student's score for each assignment.
assignments: List the assignments for each course.
students: Lists student information
classes: Lists class information
profiles: Links a student to a class

CodePudding user response：

If you need to check when value is >50% but you don't need to see it, you might use a different approach using HAVING clause

SELECT (now) AS completed, (totalassignments) AS total
FROM db
HAVING (completed/total)*100 > 50;