Re: [GENERAL] getting around---division by zero on numeric

[Richard Huxton]

Tim Nelson wrote:

I am getting division by zero on a calculated field ( sum(sales) is 0 )

It's a two-stage process, so you'll want a sub-query. Something like:

SELECT
  type,
  tot_sales,
  tot_cost
  ((tot_sales * tot_cost / tot_sales) * 100) AS percent
FROM
(
SELECT
  type, sum(sales) AS tot_sales, sum(cost) AS tot_cost
FROM
  test
GROUP BY
  type
HAVING
  sum(sales) <> 0
) AS base
;

--
  Richard Huxton
  Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend