Given a data structure such as
employees:
| id | name |
|----|------|
|1001| John |
sales:
| id | employee_id | sale_price |
|----|-------------|------------|
|1 | 1001 |15.00 |
|2 | 1001 |55.00 |
|3 | 1001 |96.00 |
Which when grouped on the employee id and SUM-ing the sale_price, the query outputs as expected:
1001 | John | 166.00
I want to write a query such as:
SELECT A.*, SUM(B.sale_price) AS total
FROM employees A
LEFT JOIN sales B
ON B.employer_id=A.id
WHERE SUM(B.sale_price) > 50 -- ERROR HERE
GROUP BY A.id
But I get an error saying Error Code: 1111. Invalid use of group function
As I understand it, it is unable to evaluate the where clause because the grouping has not been performed yet for the aggregate function to work. Is there any way to get filter on this aggregate function?