Mysql, use SUM(total) > 10 quantity in WHERE

Question

I have the following MYSQL query:

SELECT SUM(A.val) AS totalvendas, B.cod AS codigo, B.nom AS Nome, B.reg AS regiao FROM venda AS A LEFT JOIN vendedor AS B ON A.ven = B.cod WHERE reg = "norte" GROUP BY A.ven ORDER BY totalvendas DESC;   

She returns me the following:

totalvendas | codigo | Nome | regiao
-----------------------------------
144         |    1   |Sr. A | Norte
93          |   10   |Sr. J | Norte
49          |   16   |Sr. Q | Norte
42          |    6   |Sr. F | Norte
30          |    5   |Sr. E | Norte
1           |   12   |Sr. M | Norte

What I want after select, is to make a new select but return only those who have SOMA greater than 45 for example, I just want to play a number in this select and can have the result only the desired. If by chance I add in the select above in the WHERE one:

AND totalvendas > 45

Thus:

SELECT SUM(A.val) AS totalvendas,B.cod AS codigo, B.nom AS Nome, B.reg AS regiao FROM venda AS A LEFT JOIN vendedor AS B ON A.ven = B.cod WHERE reg = "norte" AND totalvendas > 45 GROUP BY A.ven ORDER BY totalvendas DESC;

It becomes an invalid query... How can I solve this problem without having to store the totals in temporary tables?

Thank you.

Answer 1

Use the 'HAVING' clause as follows:

SELECT SUM(A.val) AS totalvendas,B.cod AS codigo, B.nom AS Nome, B.reg AS regiao
FROM venda AS A LEFT JOIN vendedor AS B ON A.ven =regiao
WHERE reg = "norte"
GROUP BY regiao
HAVING totalvendas > 45
ORDER BY totalvendas DESC;