MYSQL - SUM within IF

Asked

Viewed 217 times

0

Good morning guys! All right? I took a look and found nothing about it... If it’s repeated topic, I’m sorry..

Situation: I need to filter the "conditionals" by status, but these status I define from a sum of two other tables. We currently do not have a status column within the conditional table.

So, I need to use a SUM within an IF in mysql.. is it possible? It always returns me this error: #1111 - Invalid use of grouping function (GROUP). Apparently mysql does not accept SUM within IF.

Follows an excerpt from the code

select * from `condicional` left join `condicional_itens` on `condicional_itens`.`cod_venda` = `condicional`.`cod_condicional` left join `condicional_itens_status` on `condicional_itens_status`.`cod_item` = `condicional_itens`.`cod_item` where `cod_empresa` = 1 AND condicional.cod_condicional = 35.01

        and IF( 
             sum(condicional_itens_status.quantidade)
             +
             sum(condicional_itens_status.quantidade)
             =
             sum(condicional_itens.quant)
             , 1 , 0) = 1 order by `cod_condicional` desc

Yes.. the fields are repeated, but it was just for a test... When I put in fixed values, it passes. Another question came up too... Can’t I use WHERE inside the SUM? In these SUM above I need to put a tb condition, like this:

(COALESCE(sum(condicional_itens_status.quantidade WHERE condicional_itens_status.cod_item = condicional_itens.cod_item AND condicional_itens_status.status = 1) ,0))

But then the syntax error.

From now on, very grateful!

mysql if where sum

  • You may not use aggregators within the clause WHERE. Maybe it wouldn’t be better to make one subquery to design the necessary data and then filter into the query leading?

  • try to use subquery to make the comparison of your Where

1 answer

0

is.. I had tried subquery before and it didn’t go very well.. Trying here again got like this:

select *,
(SELECT COALESCE(sum(condicional_itens_status.quantidade) ,0) as devolvido FROM condicional_itens_status WHERE condicional_itens_status.cod_item = i.cod_item AND condicional_itens_status.status = 1)  as devolvido,
(SELECT COALESCE(sum(condicional_itens_status.quantidade) ,0) as vendido FROM condicional_itens_status WHERE condicional_itens_status.cod_item = i.cod_item AND condicional_itens_status.status = 2)  as vendido
    from `condicional` 
    left join `condicional_itens` i on i.`cod_venda` = `condicional`.`cod_condicional` left join `condicional_itens_status` on `condicional_itens_status`.`cod_item` = i.`cod_item` 
        where `cod_empresa` = 1 
        AND condicional.cod_condicional = 61.01
            AND IF(
                devolvido > 0
             , 1 , 0) = 1
                group by condicional.cod_condicional order by `cod_condicional` desc;

but then it returns error:

1054 - Unknown returned column in 'Where clause'

It returns the "returned" and "sold" right... but how I would use them within the IF?

Browser other questions tagged mysql if where sum

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