|
Home > Archive > microsoft.public.sqlserver.server > October 2002 > Re: Query stops at Division By Zero-DOESN'T WORK
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: Query stops at Division By Zero-DOESN'T WORK
|
|
|
| Please help. I'm having trouble with the syntax using the solution you
provided. I'm not exactly sure where the IF/BEGIN/END constructs should
appear in this SELECT query. I forgot to mention there is WHERE
statement in my SELECT query, which I left out of my example. Not sure
if that makes a difference. Everything I've tried, has returned the
following SQL Server error message:
"An aggregate may not appear in the WHERE clause unless it is in a
subquery contained in a HAVING clause or a select list, and the column
being aggregated is an outer reference."
Here's what I tried, as well as a dozen different variations, and still
got the same error message on all variations:
IF Count(Item1) + Count(Item2)) as Item1Pct > 0
BEGIN
SELECT
COUNT(item1) AS CountOfItem1,
COUNT(item2) AS CountOfItem2,
SUM(Item1_amt) AS SumofItem1Amt,
SUM(Item2_amt) AS SumofItem2Amt,
convert(decimal, Count(item1)) + Count(item2) as
RowTTL,
convert(decimal, Sum(Item1_amt)) + Sum(Item2_amt) as
RowAMT,
convert(decimal, Count(Item1)) / (Count(Item1) +
Count (Item2)) as Item1Pct,
Store
WHERE salesday between '#url.date1#' and '#url.date2#'
GROUP BY Store
END
I've tried moving the BEGIN/END statements around, and the IF statement,
but still get the same error.
I was able to get a partial solution, by adding the below "HAVING"
statement the end of my "original" query:
HAVING Count(Item1) + Count(Item2) > 0
This allows the query to execute for all stores, EXCEPT those that
didn't have any sales of Item1 and Item2. I need ALL stores to show up
on the report, even if they didn't have any sales of Item1 or Item2. In
fact, that's the most important part of the query, to find the stores
that are NOT selling the items.
I would GREATLY APPRECIATE any help with the syntax on using the
IF/BEGIN/END constructs in this SELECT query. Thank you very much.
Gary
==============
Ross wrote:
> It is always good practice to test the divisor before you
> try to do a division, i.e.,
> IF Count(Item1) + Count(Item2)) as Item1Pct > 0
> BEGIN
> division and other stuff
> END
> That way you never get a division by zero system error.
>
> >-----Original Message-----
> >I've been running queries on SQL Server 2000, that
> calculate a
> >percentage in the SQL, without any problems.
> >
> >But, yesterday, I ran in to a problem. When a "division
> by zero" is
> >encountered, the query stops processing, at that record,
> and does NOT
> >provide an error message. It returns all the records, up
> to the one
> >where the division by zero occurred.
> >
> >I need to find some way to account for this, so the query
> continues
> >processing ALL the records, and returns 0% or 0 for
> the "division by
> >zero" instances. Here's the SQL:
> >
> >SELECT
> >
> >COUNT(item1) AS CountOfItem1,
> >COUNT(item2) AS CountOfItem2,
> >SUM(Item1_amt) AS SumofItem1Amt,
> >SUM(Item2_amt) AS SumofItem2Amt,
> >convert(decimal, Count(item1)) + Count(item2) as RowTTL,
> >convert(decimal, Sum(Item1_amt)) + Sum(Item2_amt) as
> RowAMT,
> >convert(decimal, Count(Item1)) / (Count(Item1) + Count
> (Item2)) as
> >Item1Pct,
> >Store
> >WHERE salesday between '#url.date1#' and '#url.date2#'
> >GROUP BY Store
> >
> >As you can see, I'm counting up how many of ITEM1 and
> ITEM2 were sold,
> >and the dollar value of the sales of ITEM1 and ITEM2.
> >
> >Then, I'm adding up the 2, to get a Total Count and Total
> Amount. This
> >part works fine.
> >
> >The problem occurs in the next to last statement, where
> it's dividing.
> >If the Count of Item 1 + Count of Item 2 = Zero, then
> it's dividing by
> >zero. Processing stops, and the query only returns
> results for the
> >"Stores" up to the point where the division by zero
> occurs. I should
> >get 85 stores, but instead, the query returns 18 stores,
> because at the
> >19th store, they didn't sell ANY Item1 or Item2.
> >
> >Is there some way around this? I would greatly
> appreciate any
> >suggestions. I've just never run across this before, and
> have played
> >around with sub-queries, but can't seem to get anything
> to work. Thank
> >you.
> >
> >Gary
> >
> >
| |
| Scott Morris 2002-10-05, 9:12 pm |
| <snip>
I having trouble following your posts so I'll go back to your original post.
First, what are you selecting from? The query posted shouldn't work at all
without it. Beyond that, I would rewrite it using a derived table to help
simplify the post-aggregation processing. The first step is to create a
query using the derived table. Something like:
SELECT dv1.*
FROM ( SELECT Store,
COUNT(item1) as count_item1, COUNT(item2) as count_item2,
SUM(Item1_amt) as sum_item1amt, SUM(Item2_amt) as sum_item2amt
FROM ????
GROUP BY Store) as dv1
ORDER by dv1.Store
Next, add your processing logic to it. Something like:
SELECT dv1.*,
CAST (dv1.count_item1+ dv1.count_item2 as DECIMAL(?)) as RowTTL,
CAST (dv1.sum_item1amt + dv1.sum_item2amt as DECIMAL(?)) as RowAMT,
CASE dv1.count_item1 + dv1.count_item2
WHEN 0 THEN 0
ELSE dv1.count_item1 / dv1.count_item1 + dv1.count_item2 END as Item1Pct
FROM ( .... ) as dv1
ORDER by dv1.Store
Note that I did not include the derived table in the 2nd query in order to
highlight the changes to the query. You'll have to accomodate any typos or
typing mistakes as I can't exactly test this without your tables and data.
Also, I did not cast the CASE expression to any datatype - you should do so
for completeness/correctness. Another thing that may have an impact is the
presence of NULLs in the data - this code assumes that there are none.
In the future, it would also help to post the exact (and entire) query that
you are having trouble with, along with the DDL for the tables/objects
involved in the query.
| |
|
| Thank you for your help. I apologize for not including the entire query. I'll
post it below. But your solution involves running 2 SELECT queries, rather than
1, correct? I'm not familiar with the "dvl.*", and not sure if this is exactly
what it supposed to be there, or a reference to something else?
Here is the original SQL, which works fine, but stops listing stores when it
comes across a division by zero situation, which is where
COUNT(Item1) + COUNT(Item2) = 0.
SELECT
COUNT(Item1) AS CountOfItem1,
COUNT(Item2) AS CountOfItem2,
SUM(Item1_amt) AS SumofItem1Amt,
SUM(Item2_amt) AS SumofItem2Amt,
convert(decimal, Count(Item1)) + Count(Item2) as
RowTTL,
convert(decimal, Sum(Item1_amt)) + Sum(Item2_amt) as
RowAMT,
convert(decimal, Count(Item1)) / (Count(Item1) +
Count(Item2)) as Item1Pct,
store
FROM Store_Sales
WHERE ( location = '#url.location#') AND
( day BETWEEN '#url.date1#' and '#url.date2#' )
GROUP BY store
ORDER BY store
As you can see, when Count(Item1) + Count(Item2) = 0, then you have a division
by zero, and the query only returns stores just prior to the store that didn't
have any sales.
I need to have ALL stores display on the report, especially the ones that didn't
have ANY sales, so we can see which ones are not selling the items.
I didn't realize this would be so complex. But, I would GREATLY appreciate any
help with the syntax, whether using your example of running 2 SELECT queries, or
the first suggestion to use an IF/BEGIN/END construct. Thanks again.
Gary
==========
Scott Morris wrote:
> <snip>
>
> I having trouble following your posts so I'll go back to your original post.
> First, what are you selecting from? The query posted shouldn't work at all
> without it. Beyond that, I would rewrite it using a derived table to help
> simplify the post-aggregation processing. The first step is to create a
> query using the derived table. Something like:
>
> SELECT dv1.*
> FROM ( SELECT Store,
> COUNT(item1) as count_item1, COUNT(item2) as count_item2,
> SUM(Item1_amt) as sum_item1amt, SUM(Item2_amt) as sum_item2amt
> FROM ????
> GROUP BY Store) as dv1
> ORDER by dv1.Store
>
> Next, add your processing logic to it. Something like:
>
> SELECT dv1.*,
> CAST (dv1.count_item1+ dv1.count_item2 as DECIMAL(?)) as RowTTL,
> CAST (dv1.sum_item1amt + dv1.sum_item2amt as DECIMAL(?)) as RowAMT,
> CASE dv1.count_item1 + dv1.count_item2
> WHEN 0 THEN 0
> ELSE dv1.count_item1 / dv1.count_item1 + dv1.count_item2 END as Item1Pct
> FROM ( .... ) as dv1
> ORDER by dv1.Store
>
> Note that I did not include the derived table in the 2nd query in order to
> highlight the changes to the query. You'll have to accomodate any typos or
> typing mistakes as I can't exactly test this without your tables and data.
> Also, I did not cast the CASE expression to any datatype - you should do so
> for completeness/correctness. Another thing that may have an impact is the
> presence of NULLs in the data - this code assumes that there are none.
>
> In the future, it would also help to post the exact (and entire) query that
> you are having trouble with, along with the DDL for the tables/objects
> involved in the query.
--
Gary Higgins
Chicago, IL USA
garyhig@earthlink.net
http://www.planetgary.net
+++
"If you always know what's on the road ahead,
it's not worth the journey." - Dante's Inferno
+++
| |
| Scott Morris 2002-10-05, 9:12 pm |
| I was trying to show you how to develop the query in steps. The 2nd query is
the one you want to use. I've updated it with the additional information
needed to make it work. The dv1 reference is the name of the derived table -
probably a topic that beyond your understanding at this point.
SELECT dv1.*,
CAST (dv1.count_item1+ dv1.count_item2 as DECIMAL(?)) as RowTTL,
CAST (dv1.sum_item1amt + dv1.sum_item2amt as DECIMAL(?)) as RowAMT,
CASE dv1.count_item1 + dv1.count_item2
WHEN 0 THEN 0
ELSE dv1.count_item1 / dv1.count_item1 + dv1.count_item2 END as Item1Pct
FROM (SELECT Store,
COUNT(item1) as count_item1, COUNT(item2) as count_item2,
SUM(Item1_amt) as sum_item1amt, SUM(Item2_amt) as sum_item2amt
FROM Store_Sales
WHERE ( location = '#url.location#') AND ( day BETWEEN '#url.date1#' and
'#url.date2#' )
GROUP BY Store) as dv1
ORDER by dv1.Store
One thing to remember is that this will NOT produce the desired results if
all stores do not have at least one row in the Store_Sales table that
satisfy the WHERE clause. There is probably a table named Store (or
Stores). If you want a row in the results for all stores, regardless of the
presence of matching rows in the Store_Sales table, then you'll need another
query. This is the situation where the derived table becomes more useful.
I can take a hack at this query if you need it.
"Gary" <gary.nospam@earthlink.net> wrote in message
news:3D8B347C.E9793B9F@earthlink.net...
> Thank you for your help. I apologize for not including the entire query.
I'll
> post it below. But your solution involves running 2 SELECT queries,
rather than
> 1, correct? I'm not familiar with the "dvl.*", and not sure if this is
exactly
> what it supposed to be there, or a reference to something else?
>
> Here is the original SQL, which works fine, but stops listing stores when
it
> comes across a division by zero situation, which is where
> COUNT(Item1) + COUNT(Item2) = 0.
>
> SELECT
> COUNT(Item1) AS CountOfItem1,
> COUNT(Item2) AS CountOfItem2,
> SUM(Item1_amt) AS SumofItem1Amt,
> SUM(Item2_amt) AS SumofItem2Amt,
> convert(decimal, Count(Item1)) + Count(Item2) as
> RowTTL,
> convert(decimal, Sum(Item1_amt)) + Sum(Item2_amt) as
> RowAMT,
> convert(decimal, Count(Item1)) / (Count(Item1) +
> Count(Item2)) as Item1Pct,
> store
>
> FROM Store_Sales
> WHERE ( location = '#url.location#') AND
> ( day BETWEEN '#url.date1#' and '#url.date2#' )
>
> GROUP BY store
> ORDER BY store
>
> As you can see, when Count(Item1) + Count(Item2) = 0, then you have a
division
> by zero, and the query only returns stores just prior to the store that
didn't
> have any sales.
>
> I need to have ALL stores display on the report, especially the ones that
didn't
> have ANY sales, so we can see which ones are not selling the items.
>
> I didn't realize this would be so complex. But, I would GREATLY
appreciate any
> help with the syntax, whether using your example of running 2 SELECT
queries, or
> the first suggestion to use an IF/BEGIN/END construct. Thanks again.
>
> Gary
> ==========
> Scott Morris wrote:
>
> > <snip>
> >
> > I having trouble following your posts so I'll go back to your original
post.
> > First, what are you selecting from? The query posted shouldn't work at
all
> > without it. Beyond that, I would rewrite it using a derived table to
help
> > simplify the post-aggregation processing. The first step is to create a
> > query using the derived table. Something like:
> >
> > SELECT dv1.*
> > FROM ( SELECT Store,
> > COUNT(item1) as count_item1, COUNT(item2) as count_item2,
> > SUM(Item1_amt) as sum_item1amt, SUM(Item2_amt) as sum_item2amt
> > FROM ????
> > GROUP BY Store) as dv1
> > ORDER by dv1.Store
> >
> > Next, add your processing logic to it. Something like:
> >
> > SELECT dv1.*,
> > CAST (dv1.count_item1+ dv1.count_item2 as DECIMAL(?)) as RowTTL,
> > CAST (dv1.sum_item1amt + dv1.sum_item2amt as DECIMAL(?)) as RowAMT,
> > CASE dv1.count_item1 + dv1.count_item2
> > WHEN 0 THEN 0
> > ELSE dv1.count_item1 / dv1.count_item1 + dv1.count_item2 END as
Item1Pct
> > FROM ( .... ) as dv1
> > ORDER by dv1.Store
> >
> > Note that I did not include the derived table in the 2nd query in order
to
> > highlight the changes to the query. You'll have to accomodate any typos
or
> > typing mistakes as I can't exactly test this without your tables and
data.
> > Also, I did not cast the CASE expression to any datatype - you should do
so
> > for completeness/correctness. Another thing that may have an impact is
the
> > presence of NULLs in the data - this code assumes that there are none.
> >
> > In the future, it would also help to post the exact (and entire) query
that
> > you are having trouble with, along with the DDL for the tables/objects
> > involved in the query.
>
> --
> Gary Higgins
> Chicago, IL USA
> garyhig@earthlink.net
> http://www.planetgary.net
> +++
> "If you always know what's on the road ahead,
> it's not worth the journey." - Dante's Inferno
> +++
>
>
| |
|
| FYI, everything is working fine, with the CASE statement, shown below.
What I don't understand, is this. CASE is just another way of saying
IF/THEN/ELSEIF/ELSE...etc.
Yet, SQL lets you insert a CASE clause, right in the middle of a SELECT
statement, BUT, it won't let you insert an IF/THEN/ELSE clause in the middle of
a SELECT statement (using Ross' example).
Is this just the way SQL works? Or have I not gotten the finer points of using
IF/THEN/ELSE in a SELECT query? And, if you have to use BEGIN/END with an
IF/THEN/ELSE when more than 1 condition applies, that adds to the complexity.
My Advanced Transact SQL book for SQL Server 2000, seems to indicate that
BEGIN/END can only be used for Stored Procedures. At least, it's only mentioned
in the chapter that discusses Stored Procedures.
I'm not writing a stored procedure, just a basic SQL SELECT query. Anyway, if
you have any comments, or suggestions, I would welcome them. Thank you. Gary/
EXAMPLE OF WORKING SELECT QUERY THAT ACCOUNTS FOR DIVISION BY ZERO CONDITIONS,
USING "CASE">
SELECT
<everything up to the division line, then>
CASE
When Count(item1) = 0 AND Count(item2) = 0
THEN
convert(decimal, Count(item1)) / 1
ELSE
convert(decimal, Count(item1)) / ( Count(item1) + Count(item2) )
END As PctItem1,
STORE
FROM....
GROUP BY...
ORDER BY...
===================
Scott Morris wrote:
> I was trying to show you how to develop the query in steps. The 2nd query is
> the one you want to use. I've updated it with the additional information
> needed to make it work. The dv1 reference is the name of the derived table -
> probably a topic that beyond your understanding at this point.
>
> SELECT dv1.*,
> CAST (dv1.count_item1+ dv1.count_item2 as DECIMAL(?)) as RowTTL,
> CAST (dv1.sum_item1amt + dv1.sum_item2amt as DECIMAL(?)) as RowAMT,
> CASE dv1.count_item1 + dv1.count_item2
> WHEN 0 THEN 0
> ELSE dv1.count_item1 / dv1.count_item1 + dv1.count_item2 END as Item1Pct
> FROM (SELECT Store,
> COUNT(item1) as count_item1, COUNT(item2) as count_item2,
> SUM(Item1_amt) as sum_item1amt, SUM(Item2_amt) as sum_item2amt
> FROM Store_Sales
> WHERE ( location = '#url.location#') AND ( day BETWEEN '#url.date1#' and
> '#url.date2#' )
> GROUP BY Store) as dv1
> ORDER by dv1.Store
>
> One thing to remember is that this will NOT produce the desired results if
> all stores do not have at least one row in the Store_Sales table that
> satisfy the WHERE clause. There is probably a table named Store (or
> Stores). If you want a row in the results for all stores, regardless of the
> presence of matching rows in the Store_Sales table, then you'll need another
> query. This is the situation where the derived table becomes more useful.
> I can take a hack at this query if you need it.
>
> "Gary" <gary.nospam@earthlink.net> wrote in message
> news:3D8B347C.E9793B9F@earthlink.net...
> > Thank you for your help. I apologize for not including the entire query.
> I'll
> > post it below. But your solution involves running 2 SELECT queries,
> rather than
> > 1, correct? I'm not familiar with the "dvl.*", and not sure if this is
> exactly
> > what it supposed to be there, or a reference to something else?
> >
> > Here is the original SQL, which works fine, but stops listing stores when
> it
> > comes across a division by zero situation, which is where
> > COUNT(Item1) + COUNT(Item2) = 0.
> >
> > SELECT
> > COUNT(Item1) AS CountOfItem1,
> > COUNT(Item2) AS CountOfItem2,
> > SUM(Item1_amt) AS SumofItem1Amt,
> > SUM(Item2_amt) AS SumofItem2Amt,
> > convert(decimal, Count(Item1)) + Count(Item2) as
> > RowTTL,
> > convert(decimal, Sum(Item1_amt)) + Sum(Item2_amt) as
> > RowAMT,
> > convert(decimal, Count(Item1)) / (Count(Item1) +
> > Count(Item2)) as Item1Pct,
> > store
> >
> > FROM Store_Sales
> > WHERE ( location = '#url.location#') AND
> > ( day BETWEEN '#url.date1#' and '#url.date2#' )
> >
> > GROUP BY store
> > ORDER BY store
> >
> > As you can see, when Count(Item1) + Count(Item2) = 0, then you have a
> division
> > by zero, and the query only returns stores just prior to the store that
> didn't
> > have any sales.
> >
> > I need to have ALL stores display on the report, especially the ones that
> didn't
> > have ANY sales, so we can see which ones are not selling the items.
> >
> > I didn't realize this would be so complex. But, I would GREATLY
> appreciate any
> > help with the syntax, whether using your example of running 2 SELECT
> queries, or
> > the first suggestion to use an IF/BEGIN/END construct. Thanks again.
> >
> > Gary
> > ==========
> > Scott Morris wrote:
> >
> > > <snip>
> > >
> > > I having trouble following your posts so I'll go back to your original
> post.
> > > First, what are you selecting from? The query posted shouldn't work at
> all
> > > without it. Beyond that, I would rewrite it using a derived table to
> help
> > > simplify the post-aggregation processing. The first step is to create a
> > > query using the derived table. Something like:
> > >
> > > SELECT dv1.*
> > > FROM ( SELECT Store,
> > > COUNT(item1) as count_item1, COUNT(item2) as count_item2,
> > > SUM(Item1_amt) as sum_item1amt, SUM(Item2_amt) as sum_item2amt
> > > FROM ????
> > > GROUP BY Store) as dv1
> > > ORDER by dv1.Store
> > >
> > > Next, add your processing logic to it. Something like:
> > >
> > > SELECT dv1.*,
> > > CAST (dv1.count_item1+ dv1.count_item2 as DECIMAL(?)) as RowTTL,
> > > CAST (dv1.sum_item1amt + dv1.sum_item2amt as DECIMAL(?)) as RowAMT,
> > > CASE dv1.count_item1 + dv1.count_item2
> > > WHEN 0 THEN 0
> > > ELSE dv1.count_item1 / dv1.count_item1 + dv1.count_item2 END as
> Item1Pct
> > > FROM ( .... ) as dv1
> > > ORDER by dv1.Store
> > >
> > > Note that I did not include the derived table in the 2nd query in order
> to
> > > highlight the changes to the query. You'll have to accomodate any typos
> or
> > > typing mistakes as I can't exactly test this without your tables and
> data.
> > > Also, I did not cast the CASE expression to any datatype - you should do
> so
> > > for completeness/correctness. Another thing that may have an impact is
> the
> > > presence of NULLs in the data - this code assumes that there are none.
> > >
> > > In the future, it would also help to post the exact (and entire) query
> that
> > > you are having trouble with, along with the DDL for the tables/objects
> > > involved in the query.
> >
| |
| Tibor Karaszi 2002-10-05, 9:12 pm |
| Gary,
> CASE is just another way of saying
> IF/THEN/ELSEIF/ELSE...etc.
In a way, yes. But in SQL, CASE is an *expression*, not a statement. An expression is something
that returns a scalar value. In below query, you have 4 expressions (two in column list and two
in WHERE clause).
SELECT au_lname, UPPER(au_fname)
FROM authors
WHERE au_lname LIKE 'W%'
There is no CASE *statement* in TSQL. There are IF ELSE statements, though. BEGIN and END is not
at all limited to stored procedures. You use BEGIN and END together with IF ELSE and WHILE.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=...ublic.sqlserver
"Gary" <gary.nospam@earthlink.net> wrote in message news:3D8DD020.AE51BCD@earthlink.net...
> FYI, everything is working fine, with the CASE statement, shown below.
> What I don't understand, is this. CASE is just another way of saying
> IF/THEN/ELSEIF/ELSE...etc.
>
> Yet, SQL lets you insert a CASE clause, right in the middle of a SELECT
> statement, BUT, it won't let you insert an IF/THEN/ELSE clause in the middle of
> a SELECT statement (using Ross' example).
>
> Is this just the way SQL works? Or have I not gotten the finer points of using
> IF/THEN/ELSE in a SELECT query? And, if you have to use BEGIN/END with an
> IF/THEN/ELSE when more than 1 condition applies, that adds to the complexity.
> My Advanced Transact SQL book for SQL Server 2000, seems to indicate that
> BEGIN/END can only be used for Stored Procedures. At least, it's only mentioned
> in the chapter that discusses Stored Procedures.
>
> I'm not writing a stored procedure, just a basic SQL SELECT query. Anyway, if
> you have any comments, or suggestions, I would welcome them. Thank you. Gary/
> EXAMPLE OF WORKING SELECT QUERY THAT ACCOUNTS FOR DIVISION BY ZERO CONDITIONS,
> USING "CASE">
>
> SELECT
> <everything up to the division line, then>
> CASE
> When Count(item1) = 0 AND Count(item2) = 0
> THEN
> convert(decimal, Count(item1)) / 1
> ELSE
> convert(decimal, Count(item1)) / ( Count(item1) + Count(item2) )
> END As PctItem1,
> STORE
>
> FROM....
> GROUP BY...
> ORDER BY...
> ===================
|
|
|
|
|