HELP with case statement [Divide by zero error encountered.] !

Hi,
I have a case statement that has been giving me hell for the past day, can
anyone suggest another way of validating is those field have zeros or not.
The field types are numberic, looking at the table their is no Null values i
n
there a few hundred zeros (0, .000). I have tried this statement in many wa
y
still the same result.
Divide by zero error encountered.
CASE when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end as [dollar_turns],in your case statement you r not checking for dids.supplier_cost = 0
and in your else clause you are divinding by
( (dihs.qty_total) * dids.supplier_cost )
hope thishelps
rgds
abhishek
"ITDUDE27" wrote:

> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],
>|||By any chance are you rinning SQL 2005? I seem to recall someone else
having a similar issue where the optimizer evaluated the whole
statement rather than one test at a time.
Stu
ITDUDE27 wrote:
> Hi,
> I have a case statement that has been giving me hell for the past day, can
> anyone suggest another way of validating is those field have zeros or not.
> The field types are numberic, looking at the table their is no Null values
in
> there a few hundred zeros (0, .000). I have tried this statement in many
way
> still the same result.
> Divide by zero error encountered.
>
> CASE when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end as [dollar_turns],|||Hi
Your assumption is wrong :)
Let me explain it.
For simplicity I am showing the columns as a(supplier_cost)
,b(qty_total),c(revenue)
lets say the table is like this...
a b c
1 1 1
0 0 0
For all the cases the sum is going to be 1 (for all three columns)
But analyze this..
sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
its actually 1/(1*1) + 0/(0*0)
I guess maybe you are trying for something like this in your else clause..
sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
If not change your logic accordingly.
Hope this helps.
--
-Omnibuzz (The SQL GC)
http://omnibuzz-sql.blogspot.com/|||This thread illustrates exactly why one of the best ways to get help is to
include the *ACTUAL* table DDL code *AND* some sample data INSERT
statements.
No so many back-and-forths just trying to understand the issue.
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Omnibuzz" <Omnibuzz@.discussions.microsoft.com> wrote in message
news:6DEF9528-1748-4905-ACEE-0567A384126B@.microsoft.com...
> Hi
> Your assumption is wrong :)
> Let me explain it.
> For simplicity I am showing the columns as a(supplier_cost)
> ,b(qty_total),c(revenue)
> lets say the table is like this...
> a b c
> 1 1 1
> 0 0 0
> For all the cases the sum is going to be 1 (for all three columns)
> But analyze this..
> sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> its actually 1/(1*1) + 0/(0*0)
> I guess maybe you are trying for something like this in your else clause..
> sum (dids.revenue)/sum(dihs.qty_total) * sum(dids.supplier_cost)
> If not change your logic accordingly.
> Hope this helps.
> --
> -Omnibuzz (The SQL GC)
> http://omnibuzz-sql.blogspot.com/
>sql

HELP with case statement [Divide by zero error encountered.] !

I am getting the same result pandey, I might have left that of by accident.
I
have tried it many ways.
case when sum (dids.supplier_cost) = '0'
then '0'
when sum (dids.supplier_cost) IS NULL
then '0'
when sum (dihs.qty_total) = '0'
then '0'
when sum (dihs.qty_total) IS NULL
then '0'
WHEN SUM (dids.revenue) = '0'
THEN '0'
WHEN SUM (dids.revenue) IS NULL
THEN '0'
else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
end [dollar_turns],
"Abhishek Pandey" wrote:
> in your case statement you r not checking for dids.supplier_cost = 0
> and in your else clause you are divinding by
> ( (dihs.qty_total) * dids.supplier_cost )
> hope thishelps
> rgds
> abhishek
> "ITDUDE27" wrote:
>i am not sure what exactly are you trying to do here..
in your case statement you are checking "sum(dids.supplier_cost)" and other
things for 0 and handling it accordingly but in else clause you are divindin
g
by dids.supplier_cost.
say for example
dids.supplier_cost has three values
1
0
3
while sum(dids.supplier_cost) <> 0 but this doest garuntee that individual
values are also not zero.
so in this case when it goes to else clause sql server experience a divide
by zero error coz what it will try to do is this
sum( somethingvalue / somenonzerovalue + somevalue/ 0 + somevalue/somevalue)
i hope this is clear enough.
You gotta design your query in a better way. if you are having a hard time
try to post your FULL query and some1 would be able to help you out.
regards
Abhishek
"ITDUDE27" wrote:
> I am getting the same result pandey, I might have left that of by accident
. I
> have tried it many ways.
> case when sum (dids.supplier_cost) = '0'
> then '0'
> when sum (dids.supplier_cost) IS NULL
> then '0'
> when sum (dihs.qty_total) = '0'
> then '0'
> when sum (dihs.qty_total) IS NULL
> then '0'
> WHEN SUM (dids.revenue) = '0'
> THEN '0'
> WHEN SUM (dids.revenue) IS NULL
> THEN '0'
> else sum (dids.revenue/((dihs.qty_total) * dids.supplier_cost))
> end [dollar_turns],
> "Abhishek Pandey" wrote:
>

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.

Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT

Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date

Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.

* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.

* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!

* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.

* Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?

Hi Dwight,

Yes, I think so. But you'll have to provide more info first:

* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.

* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.

* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!

* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.

* Explain the business problem behind your question.

The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||If I had to do it, I'd probably start with the following:

SELECT DISTINCT employee,
(SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
= maintable.employee GROUP BY employee) as ActiveProjectCount,
(SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
TotalProjectCountPerEmployee
FROM projectdb AS maintable

which would yield something like:

Joe510
Mary78
BrianNull1

I would then look at that Null, say Naaaah... and go about doing it right :)

FN

dwight0 wrote:

> I am having a problem with a query,
> I am not sure if i would use a join or a subquery to complete this
> problem.
> I have two queries, and i need to divide one by the other, but i cant
> seem to get any
> type of join to work with them.
> Here is the situation.
> I have a projectDB table that has a list of different projects for
> each employee to work on.
> Each project has an employee assigned to it.
> The start date is null until the employee starts to work on it.
> I want to find how many percent of all their projects that each
> employee is working on.
> In other words:
> I want to divide query A by query B to see how many percent of
> projects each employee is working on.
>
> Query A count of projects that are being worked because they have a
> date per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
> Query B: Total amount of project per employee:
> SELECT employee, COUNT(employee) AS cnt
> FROM projectDB
> GROUP BY employee, project_start_date
> Any ideas?|||Oh, I may also look at that first subquery and wonder why the heck I put
a GROUP BY clause when I didn't need one.

FN

fn wrote:

> If I had to do it, I'd probably start with the following:
> SELECT DISTINCT employee,
> (SELECT COUNT(*) FROM projectdb WHERE startdate IS NOT NULL AND employee
> = maintable.employee GROUP BY employee) as ActiveProjectCount,
> (SELECT COUNT(*) FROM projectdb WHERE employee = maintable.employee) AS
> TotalProjectCountPerEmployee
> FROM projectdb AS maintable
> which would yield something like:
> Joe 5 10
> Mary 7 8
> Brian Null 1
> I would then look at that Null, say Naaaah... and go about doing it
> right :)
> FN
>
> dwight0 wrote:
>> I am having a problem with a query, I am not sure if i would use a
>> join or a subquery to complete this
>> problem.
>> I have two queries, and i need to divide one by the other, but i cant
>> seem to get any
>> type of join to work with them.
>> Here is the situation.
>> I have a projectDB table that has a list of different projects for
>> each employee to work on.
>> Each project has an employee assigned to it.
>> The start date is null until the employee starts to work on it.
>> I want to find how many percent of all their projects that each
>> employee is working on.
>> In other words:
>> I want to divide query A by query B to see how many percent of
>> projects each employee is working on.
>>
>>
>> Query A count of projects that are being worked because they have a
>> date per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>> HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>>
>>
>> Query B: Total amount of project per employee:
>> SELECT employee, COUNT(employee) AS cnt
>> FROM projectDB
>> GROUP BY employee, project_start_date
>>
>> Any ideas?

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?
On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help with 2 queries / Join problem

I am having a problem with a query,
I am not sure if i would use a join or a subquery to complete this
problem.
I have two queries, and i need to divide one by the other, but i cant
seem to get any
type of join to work with them.
Here is the situation.
I have a projectDB table that has a list of different projects for
each employee to work on.
Each project has an employee assigned to it.
The start date is null until the employee starts to work on it.
I want to find how many percent of all their projects that each
employee is working on.
In other words:
I want to divide query A by query B to see how many percent of
projects each employee is working on.
Query A count of projects that are being worked because they have a
date per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
HAVING (NOT (project_start_date IS NULL)) //notice the NOT
Query B: Total amount of project per employee:
SELECT employee, COUNT(employee) AS cnt
FROM projectDB
GROUP BY employee, project_start_date
Any ideas?On 24 Jun 2004 14:59:48 -0700, dwight0 wrote:

>I am having a problem with a query,
>I am not sure if i would use a join or a subquery to complete this
>problem.
>I have two queries, and i need to divide one by the other, but i cant
>seem to get any
>type of join to work with them.
>Here is the situation.
>I have a projectDB table that has a list of different projects for
>each employee to work on.
>Each project has an employee assigned to it.
>The start date is null until the employee starts to work on it.
>I want to find how many percent of all their projects that each
>employee is working on.
>In other words:
>I want to divide query A by query B to see how many percent of
>projects each employee is working on.
>
>Query A count of projects that are being worked because they have a
>date per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>HAVING (NOT (project_start_date IS NULL)) //notice the NOT
>
>Query B: Total amount of project per employee:
>SELECT employee, COUNT(employee) AS cnt
>FROM projectDB
>GROUP BY employee, project_start_date
>Any ideas?
Hi Dwight,
Yes, I think so. But you'll have to provide more info first:
* What RDBMS is this for? I noticed you crossposted in both SQL Server and
Oracle groups, but both have many proprietary additions (or even changes)
to the ANSI standard SQL syntax.
* What is the actual structure of your table. Please post your DDL (CREATE
TABLE statements, including all constraints) for all tables that are
relevant for the query. Irrelevant columns may be omitted.
* Give some sample data. Do so in the form of INSERT statements. I love to
cut and paste your statements, so I can run some tests. I hate to do lots
of typing myself. Remember that I, and many others, are helping you and
others in our free time - don't make us spend more of our time than
necessary!
* Tell us what output you expect, based on the sample data you provided.
Explain why that should be the output and not anything else. Don't forget
to include the formulas used.
* Explain the business problem behind your question.
The last part (the business problem) is the only thing I can distill from
your message. If you provide the rest, I'm sure I (or someone else) will
be able to help you out.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)