if statement to correct divide by zero

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

if statement to correct divide by zero

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-05-02 : 11:04:43

I have this statement, sometimes the Dominator can be zero so i tried this if statement but the error i'm getting is:

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.(42000,147)

This is the query i'm using:

SELECT DBO.THIT_RATIO_DETL.F_DIVISION_NO,
Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END) AS BOUND, 
Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE,

Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END)*1.0 / If(Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)= 0 Then 1 else Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)) as HIT_RATIO
FROM  dbo.THIT_RATIO_DETL
Group by dbo.thit_ratio_detl.F_DIVISION_NO

Not sure if in SQL server i can correct the problem like that, any help is greatly appriciated...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 11:10:55

How about this?

Select
	THIT_RATIO_DETL.F_DIVISION_NO,
	BOUND,
	QUOTE,
	((Bound * 1.0) / case Quote when 0 then 1 else Quote End) as HIT_RATIO
From
	(
	SELECT 
		DBO.THIT_RATIO_DETL.F_DIVISION_NO,
		Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END) AS BOUND, 
		Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE
	FROM  dbo.THIT_RATIO_DETL
	Group by dbo.thit_ratio_detl.F_DIVISION_NO
	) t

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jpockets
Starting Member

45 Posts

Posted - 2007-05-02 : 11:20:37

Thanks for the reply...

I'm not sure if I follow what you gave, i don't see the division in there. I'm trying to add logic that if the Dominator is zero it will be 1, if not than take the sum of

Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 11:32:00

Have you tried running my query? I am doing exactly what your query is supposed to do. I just simplified the SUM() part by enclosing it in the derived table, so as to avoid repeating SUM() expressions in the CASE statement.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

jpockets
Starting Member

45 Posts

Posted - 2007-05-02 : 11:49:12

I did run it and i got an error:

The column prefix 'THIT_RATIO_DETL' does not match with a table name or alias name used in the query..

This logic if i have 4 more columns that i need to divide on can i use the same logic?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-05-02 : 11:55:11

Here is the corrected version:

Select
	F_DIVISION_NO,
	BOUND,
	QUOTE,
	((Bound * 1.0) / case Quote when 0 then 1 else Quote End) as HIT_RATIO
From
	(
	SELECT 
		DBO.THIT_RATIO_DETL.F_DIVISION_NO,
		Sum(Case When dbo.THIT_RATIO_DETL.STATUS_CD ="B" OR dbo.THIT_RATIO_DETL.STATUS_CD="K" THEN 1 ELSE 0 END) AS BOUND, 
		Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD ="Q" or dbo.THIT_RATIO_DETL.STATUS_CD ="L" or dbo.THIT_RATIO_DETL.STATUS_CD ="K" THEN 1 ELSE 0 END) as QUOTE
	FROM  dbo.THIT_RATIO_DETL
	Group by dbo.thit_ratio_detl.F_DIVISION_NO
	) t

And the answer to your second question is yes.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-05-02 : 12:20:01

Do you want the result to be the value of Bound if Quote is zero? Or do you want something like:

CASE 
	WHEN Quote > 0 THEN (Bound * 1.0) / Quote
	ELSE 0.0
END AS HIT_RATIO

jpockets
Starting Member

45 Posts

Posted - 2007-05-02 : 12:21:30

Thank-you it worked i think I understand the logic is that a sub-query that you did? I'm going to try to use it with the other columns, thank-you again!

jpockets
Starting Member

45 Posts

Posted - 2007-05-02 : 12:47:38

Logic seems to working well with the other columns, one question at the end of the query you have ) t not sure what that stands for.... Sorry if that is an easy question very new...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources