Help with Aggregate Functions

[renee35]

I have the below formula:

Case when sum(isnull(o.payrate,0)) = 0 then 0 else sum(o.payrate)/count (distinct o.candidateid)

What I need to say is:

Case when sum(isnull(count (distinct o.candidateid),0)) = 0 then 0 else sum(o.payrate)/count (distinct o.candidateid)

But I can't use count (distinct o.candidateid) in the sum(Isnull) function because it returns an error about can't use within an aggregate function.

Please Help!!

Thanks a bunch!!

-T

 

[nelco]

Why do you want to use sum and count in the same expression?

 

[bborissov]

The formula is wrong. What if you didn't have any Candidates?
Then you will get divide by zero error.
Also there is no need to use SUM(ISNULL...)) SUM will skip all NULL values (of course if you have at last ONE record.
The bottom example is assumed that at least one candidate have NOT NULL payrate:

CASE WHEN COUNT(distinct o.candidateid) = 0
          THEN 0
     ELSE
          sum(o.payrate)/COUNT(distinct o.candidateid)
END AS ???

If every candidates have NULL as payrate the that will return NULL!!!
If you didn't have any candidates you will get ZERO as a result, because you will be falling in the first CASE.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

Even easier....

[tt][blue]
IsNull(Sum(o.payrate)/NullIf(Count(Distinct o.candidateid), 0), 0)
[/blue][/tt]

Ok... well... maybe not easier, but it's another way of doing it.

Basically, you want to protect yourself from Divide By Zero. This is where NullIf comes in.

If the count is 0, the NullIf will return 0. Divide by NULL is null, so you wrap the whole thing in IsNull so that it returns 0

Declare @Denominator Int

Set @Denominator = 0

Select NullIf(@Denominator, 0)                [green]-- Returns NULL[/green]
Select 3 / NullIf(@Denominator, 0)            [green]-- Returns NULL[/green]
Select IsNull(3 / NullIf(@Denominator, 0), 0) [green]-- Returns 0[/green]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

George,
Sometimes I really hate you!
Every time I thought I come to some real invention with a few hundreds lines of code then you come and do the same thing with ONE line!!!!!!!!!

but THANK YOU!!!!!!!!!!! Really! Every time I read your posts I learn HOW to do something easier

But I got you here )))
(and that is just because of my jealous eyes)

If the count is 0, the NullIf will return 0

Typo
But NULLIF(0,0) will return NULL not 0


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.

 

[gmmastros]

You're right. Of course. Really... it's all my fault for trying to explain my own code!



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

Like Franky Miller once sang:
Jealousy.... (opened my eyes )

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.