Calculate rate based on condition using postgresql

Question

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

Can you post the table you're working with and the desired result? — DannySlor, Sep 04 '22 at 16:13

score 2 · Accepted Answer · answered Sep 04 '22 at 16:44

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

score 1 · Answer 2 · answered Sep 04 '22 at 16:20

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

Calculate rate based on condition using postgresql

2 Answers2