CODEWITHSUNDEEP
sqldatabase
nearly_lunchtime
nearly_lunchtime

Reputation: 12933

Getting out of divide by zero in simple query

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

Upvotes: 0

Views: 278

Answers (4)

Brandon Montgomery
Brandon Montgomery

Reputation: 6986

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

Where
  order.value <> 0

Upvotes: 2

SQLMenace
SQLMenace

Reputation: 135111

See here: SQL Server efficient handling of divide by zero

Upvotes: 3

Binary Worrier
Binary Worrier

Reputation: 51711

Add 

having sum(order.value) <> 0

After your group by

Upvotes: 2

Quassnoi
Quassnoi

Reputation: 425613

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

Upvotes: 2

Related Questions