Question 114823 in PostgreSQL Question posted in
The official documentation can be found here.

Calculate rate based on condition using postgresql

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

Tags:

2

Answers


  1. 0

    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
    Login or Signup to reply.
  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search