Doubt referring to Count in SQL command

Question

Doubt referring to Count in SQL command

Navigation

#1 by (5 votes)
#2 by (0 votes)

3

I need to count the amount of supplies available in stock according to your codigoSuprimento , so 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 it to select only those that still have at least one available ( usado = '0' ) when the supply exists in the stock but has none available it does not return anything to me, it is possible that it returns me 0 if there are none available in stock at that time?

sql count

asked by anonymous 22.08.2017 / 16:11

2 answers

0

I do not know if you would have much impact on performance but 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'

22.08.2017 / 16:33

Extract multiple values from a single variable in python Randomly display data from a database

score 5 · Accepted Answer

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

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