Sub select with COUNT and SUM

Question

Sub select with COUNT and SUM

Navigation

#1 by (4 votes)

2

I have the following query :

SELECT 
    s1.suprimento, 
    e1.numeroserie, 
    s1.capacidade, 
    e1.numeronotafiscal, 
    e1.valorunitario, 
    e1.datanotafiscal
FROM 
    public.estoque e1, 
    public.suprimento s1,
    public.fornecedor 
WHERE
    e1.datanotafiscal is not null and
    s1.codigo = e1.codigosuprimento and 
    e1.codigofornecedor = public.fornecedor.codigo and 
    e1.numeronotafiscal = '19255'
ORDER BY 
    e1.datanotafiscal desc,s1.suprimento

Where do I get the following return:

How could I make a count to return the quantity of each supply of that return, and sum the unit value of each item?

sql postgresql

asked by anonymous 19.03.2018 / 13:53

1 answer

Calculating the maximum value for an expression of natural numbers using Python Conditional javascript call in html5

score 4 · Accepted Answer

You need to remove the e1.numeroserie of the query (since it is unique) and group it by the remaining columns:

SELECT 
    s1.suprimento,
    s1.capacidade, 
    e1.numeronotafiscal, 
    e1.valorunitario, /*sum(e1.valorunitario) -- substituir consulta do valorunitario caso queira a soma*/
    count(*) as total
FROM 
    public.estoque e1, 
    public.suprimento s1,
    public.fornecedor 
WHERE 
    e1.datanotafiscal is not null and
    s1.codigo = e1.codigosuprimento and 
    e1.codigofornecedor = public.fornecedor.codigo and 
    e1.numeronotafiscal = '19255'
GROUP BY
    s1.suprimento, 
    s1.capacidade, 
    e1.numeronotafiscal, 
    e1.valorunitario, /*remover do group by caso queira soma dos valores*/
    e1.datanotafiscal
ORDER BY 
    e1.datanotafiscal desc,s1.suprimento

Online sample SQLFiddle

Detail :

The unit value is the same in return, I do not understand why you need the "sum of the unit values of each item."