Merge the output of two Select’s - Postgresql commands

Asked

Viewed 1,510 times

2

My need is to merge the result of two select commands so that I get everything back at once, for example in this command:

SELECT codigosuprimento, suprimento, count (codigosuprimento) quantidade, estoqueminimo, 
count (codigosuprimento) - estoqueminimo as saldo
    FROM public.estoque, public.suprimento where usado = '0' and codigosuprimento = 155 and
    public.estoque.codigosuprimento = public.suprimento.codigo group by codigosuprimento, suprimento, estoqueminimo order by suprimento;

I bring the current situation of the code supply 155 in stock, My return is like this:

inserir a descrição da imagem aqui

And in that command :

SELECT public.estoque.codigosuprimento,COUNT (public.estoque.codigosuprimento) as qtdSuprimento
FROM public.entrega, public.entregaitem, public.estoque
WHERE public.entrega.codigo = public.entregaitem.codigoentrega and codigosuprimento = 155 and
public.entregaitem.codigoestoque = public.estoque.codigo and public.entrega.data >= CURRENT_DATE - 30 
GROUP BY public.estoque.codigosuprimento  
ORDER BY codigosuprimento

I bring in how many supplies have been requisitioned in the last thirty days, and my return is like this:

inserir a descrição da imagem aqui

My need would be to unite the results so that there would be something similar to this:

codigosuprimento | suprimento | quantidade | estoqueminimo | saldo | qtdSolicitada
        155      |  50F0Z00   |     54     |        10     |   44  |       19

I did a test and I put one query thus:

SELECT codigosuprimento, suprimento, count (codigosuprimento) quantidade, estoqueminimo, 
count (codigosuprimento) - estoqueminimo as saldo, 
(SELECT COUNT (public.estoque.codigosuprimento) as qtdSuprimento
FROM public.entrega, public.entregaitem, public.estoque
WHERE public.entrega.codigo = public.entregaitem.codigoentrega and codigosuprimento = 155 and
public.entregaitem.codigoestoque = public.estoque.codigo and public.entrega.data >= CURRENT_DATE - 30) as trinta
FROM public.estoque, public.suprimento where usado = '0' and codigosuprimento = 155 and
public.estoque.codigosuprimento = public.suprimento.codigo group by codigosuprimento, suprimento, estoqueminimo order by suprimento;

And so it brought me the expected result, but when I went to test to bring without the condition of bringing only to the code supply 155 it has already brought me meaningless results. I tried in this way to query and brought me the wrong results:

SELECT codigosuprimento, suprimento, count (codigosuprimento) quantidade, estoqueminimo, 
count (codigosuprimento) - estoqueminimo as saldo, 
(SELECT COUNT (public.estoque.codigosuprimento) as qtdSuprimento
FROM public.entrega, public.entregaitem, public.estoque
WHERE public.entrega.codigo = public.entregaitem.codigoentrega and
public.entregaitem.codigoestoque = public.estoque.codigo and public.entrega.data >= CURRENT_DATE - 30) as trinta
FROM public.estoque, public.suprimento where usado = '0' and
public.estoque.codigosuprimento = public.suprimento.codigo group by codigosuprimento, suprimento, estoqueminimo order by suprimento;
sql select
  • 1

    Ever tried to give a (select1...) UNION (select2...)?

  • @Zulian already, but to use the UNION both returns must have the same number of columns

  • 1

    Even putting the "additional" columns as null?

  • The problem is that it brought the content of the second select in the second row and I need it to be in the same

  • A subselect becomes but costly for db but I think it would suit you

  • Just like I put in my question @Caiqueromero?

  • @Caiqueromero you would have some example of how to do with subselects?

  • Instead of you fill 155 you column the code column of the first select

  • @Caiqueromero, how so?

  • I demonstrated in the answer, see if I could help you

Show 5 more comments

1 answer

1


SELECT codigosuprimento
  , suprimento
  , count (codigosuprimento) quantidade
  , estoqueminimo
  , count (codigosuprimento) - estoqueminimo AS saldo
  , (
     SELECT COUNT (estoque2.codigosuprimento) as qtdSuprimento
     FROM public.entrega
        , public.entregaitem
        , public.estoque AS estoque2 --Renomeio a tabela para relacionar ambas as querys
     WHERE public.entrega.codigo = public.entregaitem.codigoentrega 
        AND public.estoque.codigosuprimento = estoque2.codigosuprimento-- Relaciono as 2 querys
        AND public.entregaitem.codigoestoque = estoque2.codigo 
        AND public.entrega.data >= CURRENT_DATE - 30
    ) AS trinta
FROM public.estoque
   , public.suprimento 
WHERE usado = '0' 
   AND codigosuprimento = 155 
   AND public.estoque.codigosuprimento = public.suprimento.codigo 
GROUP BY codigosuprimento
  , suprimento
  , estoqueminimo
ORDER BY suprimento;

  • Perfect worked as needed, thank you very much :)

  • I’m glad it worked out :)

Browser other questions tagged sql select

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