Search code examples
sqlpostgresqlrate

SQL rate (percentage) calculation - division by zero error

Tried to user NULLIF or IFNULL functions but still receiving division by zero message.

SELECT 
client_id
,COUNT(distinct CASE WHEN status = 'failed' THEN id END) AS count_FAILS
,COUNT(distinct CASE WHEN status = 'completed' THEN id END) AS count_COMPLETED
,COUNT(distinct CASE WHEN status IN ('failed') THEN id END)
 /CAST(COUNT(CASE WHEN status = 'completed' THEN id END) AS FLOAT) 

FROM journey
GROUP BY 1

Example database and query https://dbfiddle.uk/?rdbms=postgres_10&fiddle=efc0cd25843e852ab7a3aa8fe49e6986

Is distinct necessary for such queries?

Thanks!

Solution

  • This should work:

    COUNT(distinct CASE WHEN status IN ('failed') THEN id END) * 1.0 /
NULLIF(COUNT(CASE WHEN status = 'completed' THEN id END), 0)

    You can cast to a float. I prefer to just use * 1.0.

    I note that the definition of count_completed uses count(distinct), but this ratio does not. That is perhaps a bug.

    I would be more likely to want the proportion of failed statuses overall, rather than the ratio. If that is acceptable and the COUNT(DISTINCT) is not necessary for either count, then this can be simplified to:

    avg( (status = 'failed')::int )