How to fix Division by zero in PostgreSQL

-- Example queries that cause division by zero
SELECT user_id, total_sales / total_orders FROM user_stats;
-- ERROR: division by zero (when total_orders is 0)

SELECT profit / expenses FROM financial_reports;
-- ERROR: division by zero (when expenses is 0)

SELECT (a - b) / (c - d) FROM calculations;
-- ERROR: division by zero (when c equals d)

-- Find rows where divisor is zero
SELECT id, total_orders, total_sales
FROM user_stats
WHERE total_orders = 0;

-- Count how many rows are affected
SELECT COUNT(*) as affected_rows
FROM financial_reports
WHERE expenses = 0 OR expenses IS NULL;

-- Check for derived zero values
SELECT id, (col_a - col_b) as divisor
FROM my_table
WHERE (col_a - col_b) = 0;

-- Basic NULLIF approach
SELECT
  user_id,
  total_sales / NULLIF(total_orders, 0) as avg_sale
FROM user_stats;

-- With COALESCE to provide a default
SELECT
  user_id,
  COALESCE(total_sales / NULLIF(total_orders, 0), 0) as avg_sale
FROM user_stats;

-- For multiple divisions
SELECT
  id,
  revenue / NULLIF(expenses, 0) as profit_ratio,
  profit / NULLIF(investment, 0) as roi
FROM financial_data;

-- Filter out zero divisors
SELECT
  user_id,
  total_sales / total_orders as avg_sale
FROM user_stats
WHERE total_orders > 0;

-- Multiple conditions
SELECT
  id,
  revenue / expenses as profit_margin
FROM financial_reports
WHERE expenses > 0
  AND revenue > 0
  AND report_year = 2024;

-- With aggregate functions
SELECT
  category,
  SUM(revenue) / SUM(expenses) as total_margin
FROM sales_data
WHERE expenses > 0
GROUP BY category;

-- Return NULL for zero divisors
SELECT
  user_id,
  CASE
    WHEN total_orders > 0 THEN total_sales / total_orders
    ELSE NULL
  END as avg_sale
FROM user_stats;

-- Return a default value instead of NULL
SELECT
  id,
  CASE
    WHEN expenses > 0 THEN revenue / expenses
    ELSE 0  -- or any default value
  END as profit_margin
FROM financial_reports;

-- Multi-level logic
SELECT
  id,
  category,
  CASE
    WHEN divisor > 0 THEN numerator / divisor
    WHEN divisor = 0 THEN NULL
    ELSE -1  -- Flag error conditions
  END as result
FROM calculations;

-- Ensure divisor is at least 1
SELECT
  user_id,
  total_sales / GREATEST(total_orders, 1) as avg_sale
FROM user_stats;

-- With multiple columns
SELECT
  product_id,
  total_revenue / GREATEST(total_units, 1) as avg_price
FROM inventory;

-- In aggregate queries
SELECT
  category,
  SUM(amount) / GREATEST(COUNT(*), 1) as avg_amount
FROM transactions
GROUP BY category;

-- Add CHECK constraint to ensure positive values
ALTER TABLE user_stats
ADD CONSTRAINT chk_positive_orders
CHECK (total_orders >= 0);

-- Use DEFAULT values
ALTER TABLE financial_reports
ALTER COLUMN expenses SET DEFAULT 1;

-- Add NOT NULL constraints where zero shouldn't exist
ALTER TABLE calculations
ALTER COLUMN divisor SET NOT NULL;

-- Create index for filtered queries
CREATE INDEX idx_user_stats_active
ON user_stats (user_id)
WHERE total_orders > 0;