CODEWITHSUNDEEP
sqlpostgresql
DBE7
DBE7

Reputation: 816

PostgreSQL: Division returns zeros only

I am trying to calculate conversion rates on the fly. However, the division part is causing me some trouble.

Here is my code:

SELECT
    b.owner_id AS "Owner ID",
    COUNT(DISTINCT bo.id) AS "Number of Opportunities",
    COUNT(DISTINCT CASE WHEN bo.state = 'won' THEN bo.id END) AS "Number of Wins",
    (COUNT(DISTINCT CASE WHEN bo.state = 'won' THEN bo.id END) / (COUNT(DISTINCT bo.id)) AS "Inquiry Conversion Rate"
FROM
    bookings b 
INNER JOIN
    booking_opportunities bo
    ON (bo.id = b.opportunity_id)
GROUP BY
    1;

How do I fix this problem the most efficient way?

Upvotes: 0

Views: 259

Answers (1)

Nick
Nick

Reputation: 7441

Simply cast them to NUMERIC:

SELECT
    b.owner_id AS "Owner ID",
    COUNT(DISTINCT bo.id) AS "Number of Opportunities",
    COUNT(DISTINCT CASE WHEN bo.state = 'won' THEN bo.id END) AS "Number of Wins",
    (COUNT(DISTINCT CASE WHEN bo.state = 'won' THEN bo.id END))::NUMERIC / (COUNT(DISTINCT bo.id))::NUMERIC AS "Inquiry Conversion Rate"
FROM
    bookings b 
INNER JOIN
    booking_opportunities bo
    ON (bo.id = b.opportunity_id)
GROUP BY
    1;

Upvotes: 2

Related Questions