MySql GROUP BY and Aggregate functions in WHERE clause

Question

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?

Use a `HAVING` clause. I'm voting to close as a typo. – Gordon Linoff Jul 16 '21 at 16:43 — Gordon Linoff, Jul 16 '21 at 16:43
And why not use the nicely named column: HAVING total > 50 – UncleCarl Jul 16 '21 at 16:47 — UncleCarl, Jul 16 '21 at 16:47

score 1 · Accepted Answer · answered Jul 16 '21 at 16:55

1

SELECT A.id id, SUM(B.sale_price) AS total 
FROM employees A
LEFT JOIN sales B
ON B.employee_id=A.id
GROUP BY A.id
HAVING SUM(B.sale_price) > 50

You can use having clause. There is one typo in your query. Also "select A.*" should be "A.id". Above query should work.

answered Jul 16 '21 at 16:55

aatwork

2,130
4
17

Works great thank you, I wasn't aware of the HAVING syntax, allows me to use the aliased column too `total > 50` thank you! – Reed Jul 16 '21 at 17:05
Since A.id is presumably a unique key, all other A columns are functionally dependent on it and selecting A.* is perfectly legitimate. (though it is not allowed with ONLY_FULL_GROUP_BY in mysql before 5.7 and mariadb - see https://jira.mariadb.org/browse/MDEV-11588 - through at least 10.6) – ysth Jul 16 '21 at 17:18

Meysam Asadi · Answer 2 · 2021-07-16T16:53:38.180

0

use subquery

select *
from
(select *,
      (select sum(s.sale_price) from sales s where s.employee_id = e.id and s.sale_price > 50 group by s.employee_id) as totalPrice
    from employees e) T
where totalPrice > 50

edited Jul 16 '21 at 16:53

answered Jul 16 '21 at 16:48

Meysam Asadi

6,438
3
7
17

MySql GROUP BY and Aggregate functions in WHERE clause

2 Answers2