Doubt regarding Count in SQL command

Asked

Viewed 125 times

3

I need to count the amount of supplies available in stock according to your codigoSuprimento, for this I created the following sql command:

select count(codigosuprimento) quantidade, codigosuprimento from public.estoque where usado = '0' group by codigosuprimento order by codigosuprimento

But as I put it I would like him to select only those who still have at least one available (usado = '0') when the supply exists in stock but has none available it returns me nothing, it is possible that it returns me 0 if none is available in stock at that time?

sql count

2 answers

5


How about grouping using sum() with a conditional sum:

SELECT
    sum( CASE WHEN usado = '0' THEN 1 ELSE 0 END ) quantidade,
    codigosuprimento
FROM
    public.estoque
GROUP BY
    codigosuprimento
ORDER BY
    codigosuprimento;

  • It worked perfectly for what I needed, thank you very much +1

0

I don’t know if you would have much impact on performance but you could do two selects and use Union:

SELECT COUNT(codigosuprimento) quantidade, codigosuprimento 
FROM public.estoque 
WHERE usado = '0' 
GROUP BY codigosuprimento 
ORDER BY codigosuprimento

UNION

SELECT distinct 0 as quantidade, codigosuprimento 
FROM public.estoque 
WHERE usado <> '0'

Browser other questions tagged sql count

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