MySQL, use sum quantity (total) 10 in WHERE

Question

MySQL, use sum quantity (total) 10 in WHERE

Navigation

#1 by (1 votes)

0

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;

It 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 the select, is to make a new select but to return only those that have the SOMA greater than 45 for example, I just want to play a number in this select and can only have the desired result. If by chance I add in the above select in WHERE one:

AND totalvendas > 45

So:

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 stock the totals in temporary tables?

Thank you.

mysql select where

asked by anonymous 19.05.2017 / 06:21

1 answer

Second Like does not work SQLIte UPDADE of DB information

score 1 · Accepted Answer

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;