-2

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?

Reed
  • 1,515
  • 1
  • 21
  • 38

2 Answers2

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.

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
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
Meysam Asadi
  • 6,438
  • 3
  • 7
  • 17