SQL - Problem Naming Variables

Asked

Viewed 63 times

-1

Since I don’t know how to explain my problem, I’ll give you an example:

select cod_prod, sum(quantidade) as "Quantidade 2013", sum(quantidade) as "Quantidade 2014"
from itens
group by cod_prod
having "Quantidade 2013" in ...

The above example is not working because it does not recognize the name "Quantity 2013".

My question is:

How do I find a way to differentiate one sum(quantity) from the other so that I can manipulate them differently in the having method?

Details:

Diagrama Pergunta

sql variables

  • Maybe it is the case of the use of CASE , no pun , to treat the year 20xx

  • @Motta probably case 2013 adds up the field, or zero, but here comes the problem of having, because depending on the DB it can use alias, depending on the DB has to repeat the formula. It would be important he [Dit] and add the details

  • @Motta added new details to help in understanding the problem

  • @Bacco added new details to help in understanding the problem

1 answer

2


Man, in this case, I’d say you do the sums separately, and then cross-check with the results you got on your main selection.

Something more or less like this:

select cod_prod, s1.quantidade_soma as "Quantidade 2013", s1.quantidade_soma as "Quantidade 2014"
from itens i
join ( /*seleção em que você passará o ano e o código do produto*/
        select cod_prod, sum(quantidade) as quantidade_soma, 
        from itens i1
        where to_char(data_enc,'YYYY') = '2014'
        group by cod_prod
) s1 on (s1.cod_prod = i.cod_prod) 
/* repetir para 2013*/
where s1.quantidade_soma > s2.quantidade_soma

I would also say to you first filter, and instead of from itens i main, already bring the data he asks in the statement.

Any questions, just say the word.

Browser other questions tagged sql variables

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