Trying to divide two sql subqueries but getting 0 instead of decimal-CodePudding

Trying to divide two subqueries but getting 0 instead of decimal.

SELECT

(SELECT
    COUNT(*)
FROM
    "payments"
WHERE "error" IN ('X', 'Y') AND "created_at" >= '2022-01-25')

/ 

(SELECT 
    COUNT(*)
FROM
    "payments"

WHERE "created_at" >= '2022-01-25')

CodePudding user response：

No need to do multiple selects, do conditional aggregation instead, and multiply by 1.0 to get decimals:

SELECT COUNT(*) FILTER (WHERE "error" IN ('X', 'Y')) * 1.0 / COUNT(*)
FROM "payments"
WHERE "created_at" >= '2022-01-25'

Or use AVG():

SELECT AVG(case when "error" IN ('X', 'Y') then 1.0 else 0.0 end)
FROM "payments"
WHERE "created_at" >= '2022-01-25'