PostgreSQL error code

PostgreSQL error 22012

22012 is the SQLSTATE PostgreSQL reports for division by zero, in class 22, Data Exception.

SQLSTATE	22012
Condition name	division_by_zero
Class	22 — Data Exception

Reproduce it, then fix it

These are the queries from the guide below. Run the broken query to see the error, then switch to the fixed one. Edit either freely; every run is rolled back.

SELECT
  c.first_name,
  COUNT(o.id) AS orders,
  COALESCE(SUM(o.total_amount), 0) / COUNT(o.id) AS avg_order_value
FROM demo.customers c
LEFT JOIN demo.orders o ON o.customer_id = c.id
GROUP BY c.id, c.first_name
ORDER BY c.id;

SELECT
  c.first_name,
  COUNT(o.id) AS orders,
  ROUND(COALESCE(SUM(o.total_amount), 0) / NULLIF(COUNT(o.id), 0), 2) AS avg_order_value
FROM demo.customers c
LEFT JOIN demo.orders o ON o.customer_id = c.id
GROUP BY c.id, c.first_name
ORDER BY c.id;

How to fix it

Explained step by step, with a query you can run

division by zero

In other databases

Source: PostgreSQL documentation, Appendix A