Getting out of divide by zero in simple query.

Question

This is a simplified version of a query I have. Say for each customer that has made an order, I want to know what percentage of their total order value was for green items.

There can't be multiple items in an order, but it's possible for the order value to be zero so I want to avoid division errors. (And no, I am not able to change the database in any way.)

What's the best way to do this? EDIT: Omitting zero-totals is fine actually, hence accepted answer.

SELECT order.customer,
  SUM
  (
    CASE items.color WHEN 'Green' THEN order.value
    ELSE 0 END
  ) * 100 / sum(order.value) AS percentage,
  SUM(order.value) AS total_value
FROM orders
  INNER JOIN item
    ON order.item_id = item.item_id
GROUP BY order.customer

Answer 1

SELECT order.customer,
  SUM
  (
    CASE items.color WHEN 'Green' THEN order.value
    ELSE 0 END
  ) * 100 / CASE sum(order.value) WHEN 0 THEN 1 ELSE SUM(order.value) END AS percentage,
  SUM(order.value) AS total_value
FROM orders
  INNER JOIN item
    ON order.item_id = item.item_id
GROUP BY order.customer

Answer 2

Add

having sum(order.value) <> 0

After your group by

Answer 3

See here: SQL Server efficient handling of divide by zero

Answer 4

Can you just filter out the orders where order.value = 0?

Where
  order.value <> 0