Perform calculation in SQL command

Asked

Viewed 417 times

2

I performed a command of select on my table estoque where I bring the supply model, your current amount and what is your minimum amount in stock, for that I made the query as follows:

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

It is possible to perform a calculation on this query to bring the balance of this supply, for this I would make the account quantidade - estoqueminimo, this is possible?

sql query select

  • SELECT code supply, supply, Count (code supply) quantity, stock, (SUM(quantity) - stock) AS balance FROM public.stock, public.supply Where used = '0' and public.estoque.codigo = public.suprimento.codigo group by codigo provisioning, provisioning, estoqueminimo order by provisioning;

  • what is the need for the SUM?

  • SQL does not have functions for subtraction only sum

  • The quantidade is the result of my COUNT I can’t use it. ERROR: column "quantidade" does not exist

  • Okay I didn’t get that, let me see...

  • your stock balance will be a code count ?

  • I posted my reply @Rovannlinhalis

  • I have not tested, but try -> SELECT code supply, supply, SUM ((SELECT Count (code supply) FROM stock)-stock) as balance FROM public.stock, public.supply Where used = '0' and public.estoque.codigosuprimento = public.suprimento.codigo group by codigoprovisioning, provisioning, estoqueminimo order by provisioning;

  • I asked just a question of modeling... your stock balance is a code count ? that is, every item you add into the stock is unique, that’s right ?

  • @Helderpereira posted an answer with the way he met my need

  • 1

    @Rovannlinhalis, yes each item is unique as it enters with its serial number

Show 6 more comments

1 answer

3


I figured out how to return what I need, I just needed to alter a part of the query, in the end she gets like this:

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

Thanks for all your help

Browser other questions tagged sql query select

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