Tim Nelson <[EMAIL PROTECTED]> writes:
> I am getting division by zero on a calculated field ( sum(sales) is 0 )
> and I can't find a way around this. I figured out you can't use an
> aggregate in a where, and using having the parser must (obviously)
> evaluate the select fields before consider
Richard Huxton wrote:
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: ...
Thanks. That's a cool addition to my bag of tricks.
---(end of broadcast)-
Tim Nelson <[EMAIL PROTECTED]> schrieb:
> I am getting division by zero on a calculated field ( sum(sales) is 0 ) and
> I can't find a way around this. I figured out you can't use an aggregate
> in a where, and using having the parser must (obviously) evaluate the
> select fields before consid
Tim Nelson <[EMAIL PROTECTED]> writes:
> I am getting division by zero on a calculated field ( sum(sales) is
> 0 ) and I can't find a way around this. I figured out you can't use
> an aggregate in a where, and using having the parser must
> (obviously) evaluate the select fields before considerin
On Oct 19, 2005, at 21:26 , Tim Nelson wrote:
I am getting division by zero on a calculated field ( sum(sales) is
0 ) and I can't find a way around this. I figured out you can't
use an aggregate in a where, and using having the parser must
(obviously) evaluate the select fields before con
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
On 10/19/05 8:26 AM, "Tim Nelson" <[EMAIL PROTECTED]> wrote:
> I am getting division by zero on a calculated field ( sum(sales) is 0 )
> and I can't find a way around this. I figured out you can't use an
> aggregate in a where, and using having the parser must (obviously)
> evaluate the select fi
You could use a CASE statement...
select
type,
sum(sales),
sum(cost),
CASE WHEN sum(sales) <> 0 THEN (sum(sales) * sum(cost) / sum(sales)) *
100 ELSE 0 END
from test
group by 1
However, I guess that your example is just not what you really use as
sum(sales) * sum(cos
