SQL Sum should be zero even if group by returns no rows¶

TIL to watch out for cases when I

group by
want the sum to be 0 even if there are no rows to sum

I moved the existing query to a CTE

WITH actual_sum AS (
  SELECT
    payee_id
    SUM(amount) AS total,
  FROM table
  WHERE ...
  GROUP BY payee_id
)

I added a new view with one row

, zero_row AS (
  SELECT
    :payee_id
    0 AS total
)

SELECT total_ytd_payments
FROM
  (SELECT * FROM actual_sum
  UNION ALL
  SELECT * FROM zero_row) combined
-- ensure that the non zero row is always first
ORDER BY ABS(total) DESC
LIMIT 1

Last update: 2023-04-24