Use reference in MYSQL query condition

Question

Use reference in MYSQL query condition

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (0 votes)

0

I am doing a query to calculate the balance and after calculated I want it to only bring the products whose balance is equal to 0 so I tried to assign the total value using AS only when I went to make the condition in WHERE he did not recognize

SELECT mov.Codigo, (
SUM( IF( (
mov.Tipo =  "2"
), mov.Quantidade, 0 ) ) + SUM( IF( (
mov.Tipo =  "5"
), mov.Quantidade, 0 ) ) + SUM( IF( (
mov.Tipo =  "7"
), mov.Quantidade, 0 ) ) - SUM( IF( (
mov.Tipo =  "3"
OR mov.Tipo =  "4"
OR mov.Tipo =  "6"
), mov.Quantidade, 0 ) ) + ( 
SELECT SUM( quantidade_balanco ) 
FROM balancos
WHERE produto = mov.Codigo )
) AS total
FROM movimentacao AS mov WHERE total=0 
GROUP BY mov.Codigo

1054 - Unknown column 'total' in 'where clause'

mysql

asked by anonymous 15.05.2015 / 15:21

3 answers

1

Have you tried using Having instead of where?

15.05.2015 / 15:42

0

An example that can help you is this one:

select * from (select emp.codemp from empresa as emp) as cod where cod.codemp = 1;

Any questions just let me know.

15.05.2015 / 15:51

Apply permissions after running the rsync command What is the best way to do these Triggers?

score 2 · Accepted Answer

From documentation :

You can use aliases in GROUP BY, ORDER BY, or HAVING clauses to reference column

That is, it can not be used in WHERE . But you can do with HAVING :

SELECT mov.Codigo, (
SUM( IF( (
mov.Tipo =  "2"
), mov.Quantidade, 0 ) ) + SUM( IF( (
mov.Tipo =  "5"
), mov.Quantidade, 0 ) ) + SUM( IF( (
mov.Tipo =  "7"
), mov.Quantidade, 0 ) ) - SUM( IF( (
mov.Tipo =  "3"
OR mov.Tipo =  "4"
OR mov.Tipo =  "6"
), mov.Quantidade, 0 ) ) + ( 
SELECT SUM( quantidade_balanco ) 
FROM balancos
WHERE produto = mov.Codigo )
) AS total
FROM movimentacao AS mov
GROUP BY mov.Codigo
HAVING total=0