Calculate rate based on condition using postgresql-CodePudding

I want to find the rate of negative and zero profits from a column. I tried to do it using aggregate and subquery but it doesn't seem to work as both method return 0 values. The code is as follows

SELECT
COUNT(CASE WHEN profit < 0 THEN 1
    END) AS negative_profits,
COUNT(CASE WHEN profit < 0 THEN 1
    END) / COUNT(profit),
COUNT(CASE WHEN profit = 0 THEN 1
    END) AS zero_profits,
COUNT(CASE WHEN profit = 0 THEN 1
    END) / COUNT(profit)
FROM sales;

SELECT (SELECT COUNT(*)
FROM sales
WHERE profit <= 0)/COUNT(profit) AS n_negative_profit
FROM sales;

Both query return 0 in values enter image description here

CodePudding user response：

Because you are doing integer division per docs Math operators/functions.

numeric_type / numeric_type → numeric_type

Division (for integral types, division truncates the result towards zero)

So:

select 2/5; 
0

You need to make one of the numbers float or numeric:

select 2/5::numeric;  
0.40000000000000000000

and to make it cleaner round:

select round(2/5::numeric, 2);
0.40

CodePudding user response：

Avoid integer division, which truncates (like Adrian pointed out).
Also, simplify with an aggregate FILTER expression:

SELECT count(*) FILTER (WHERE profit <= 0)::float8
     / count(profit) AS n_negative_profit
FROM   sales;

If profit is defined NOT NULL, or to divide by the total count either way, optimize further:

SELECT count(*) FILTER (WHERE profit <= 0)::float8
     / count(*) AS n_negative_profit
FROM   sales;

See:

Aggregate columns with additional (distinct) filters

CodePudding user response：

select  round(1.0*count(*)/count(case when col = 0 then 1 end),2) as zero
       ,round(1.0*count(*)/count(case when col > 0 then 1 end),2) as positive
       ,round(1.0*count(*)/count(case when col < 0 then 1 end),2) as negative
       
from    t

zero	positive	negative
5.50	2.20	2.75

Fiddle