Sub select with COUNT and SUM

Question

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 amount of each supply of that return, and sum of the unit values of each item?

Answer 1

You need to remove the e1.numeroserie of the query (since it is unique) and group by the other 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 example Sqlfiddle

Detail:

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