Filtering query results using combined columns

Question

Not sure what this method might be called

I have a table

Pupil	Payments	Price
PupilA	100	100
PupilB	100	50
PupilB	100	0
PupilC	100	20

I am running a query as follows

SELECT Pupil, SUM(Payments), (SUM(Price) - SUM(Payments)) AS Balance , SUM(Price) 
FROM LessonSchedule
Group by Pupil Order By Balance Desc

Which gives

Pupil	Payments	Price	Balance
PupilB	200	50	150
PupilC	20	100	80
PupilA	100	100	0

The problem I have with this query is that it gives all pupils, even if there is no outstanding balance.

I am looking to find a way to display only the pupils who have an outstanding balance

the desired result would look like

Pupil	Payments	Price	Balance
PupilB	200	50	150
PupilC	20	100	80

Something like

SELECT Pupil, SUM(Payments), (SUM(Price) - SUM(Payments)) AS Balance , SUM(Price) 
FROM LessonSchedule
WHERE Balance !== 0
Group by Pupil 
Order By Balance Desc

I have tried WHERE Balance !== 0 but as the Balance column does not exist in the column, the query does not work.

I have also tried WHERE (SUM(Price) - SUM(Payments)) !==0 but this gives the same result.

Is there a recommended way to achieve the desired result?

I have also tried != in place of !==

Answer 1

SELECT Pupil,
       SUM(Payments),
       (SUM(Price) - SUM(Payments)) AS Balance , 
       SUM(Price) 
   FROM LessonSchedule
   Group by Pupil 
   HAVING Balance != 0
   Order By Balance Desc

WHERE works after gathering the data, but before doing GROUP BY.
HAVING works after GROUP BY.

Since the SUMs (and other aggregates) are done by "group by", you must wait until later for checking Balance.

Note: Balance is shorthand for (SUM(Price) - SUM(Payments)) in your example, so Balance includes an aggregate.