Use reference in MYSQL query condition

Asked

Viewed 268 times

0

I am doing a query to calculate the balance and after calculated I want it to bring only the products whose balance is equal to 0 so I tried to assign the total value using the AS only when I went to make the condition on WHERE he didn’t 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

3 answers

2


Of documentation:

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

That is, it is not possible to use 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

1

Have you tried using Having instead of Where?

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 doubt just warn.

  • It’s not the same situation

  • really not the same situation but can be used as an example for you to use an alias in the Where clause, just use your imagination and apply what you want to do, I have no way to test your code and give you the answer now.

  • Yes, I agree... but as you can see I already knew this possibility as in the code I posted FROM movimentacao AS mov WHERE total=0 
GROUP BY mov.Codigo but thanks anyway ;)

  • Opa blza, try also using HAVING can help.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.