Search code examples
sqlpostgresqlnullcasecoalesce

Return string when division result is zero

I have an SQL query like this:

SELECT 
u.id,
tu.score/(CASE tu.mo_count  WHEN 0 THEN NULL ELSE tu.mo_count END) AS score_avg
FROM tournament_userscore tu
INNER JOIN users u ON u.id = tu.user_id
WHERE tournament_id = 1
ORDER BY tu.score DESC

When tu.mo_count is zero, Null has been replaced instead of zero. How can I set a string like 'EMPTY' for this line:

tu.score/(CASE tu.mo_count  WHEN 0 THEN NULL ELSE tu.mo_count END) AS score_avg

I mean when division result is zero or empty, I want to return "anything" string.

Solution

  • Try moving the CASE-statement outside:

    SELECT 
u.id,
CASE WHEN tu.mo_count = 0 THEN 'EMPTY' ELSE (tu.score/tu.mo_count)::text END as score_avg
FROM tournament_userscore tu
INNER JOIN users u ON u.id = tu.user_id
WHERE tournament_id = 1
ORDER BY tu.score DESC