Board index » delphi » Select Sum(x)/Sum(y)

Select Sum(x)/Sum(y)

If sum(y) is 0 I get an arithmetic error which stops the entire query
returning all records before the error.

When you run the same query (with same data) through a bde app using paradox
you get a field value of null (preferable).

How do I get this query to work ?

Thanks!

 

Re:Select Sum(x)/Sum(y)


Quote
Ogg wrote:
> If sum(y) is 0 I get an arithmetic error which stops the entire query
> returning all records before the error.

        Try adding:

HAVING
  SUM(Y) <> 0

        This should return no rows when SUM(Y) is 0.

        -Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Select Sum(x)/Sum(y)


The problem is I want that record....
The Sum(x)/Sum(y) is only one part of a very large select statement....I
really want that field to be null or anything for that matter - but I need
the record...

Anything I can do ?

Thanks!

"Craig Stuntz [TeamB]" <cstu...@vertexsoftware.com> wrote in message
news:3ec93d6a$1@newsgroups.borland.com...

Quote
> Ogg wrote:

> > If sum(y) is 0 I get an arithmetic error which stops the entire query
> > returning all records before the error.

> Try adding:

> HAVING
>   SUM(Y) <> 0

> This should return no rows when SUM(Y) is 0.

> -Craig

> --
> Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
> Delphi/InterBase Weblog : http://delphi.weblogs.com
> InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
> InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Re:Select Sum(x)/Sum(y)


Quote
Ogg wrote:
> Anything I can do ?

1) Return Sum(x) and Sum(y) separately and do the division on the
client.
2) If your DB server supports CASE or COALESCE, use that.

        -Craig

--
Craig Stuntz [TeamB] . Vertex Systems Corp. . Columbus, OH
Delphi/InterBase Weblog : http://delphi.weblogs.com
InterBase Perf. Monitor : http://delphi.weblogs.com/IBPerformanceMonitor
InterBase PLANalyzer 1.1: http://delphi.weblogs.com/IBPLANalyzer

Other Threads