How to calculate this kind of average task? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

How to calculate this kind of average task?

Author

Topic

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-11-03 : 07:27:37

Hi!
I have an data looking like this

LogTime | K1 | K2 | Average
2009-01-22 02:23:00.000 0 9,9 9,9
2009-01-22 02:43:00.000 NULL 9,9 NULL
2009-01-22 02:53:00.000 10 9,9 9,95
2009-01-22 03:03:00.000 NULL NULL NULL
2009-01-22 03:13:00.000 NULL NULL NULL
2009-01-22 03:23:00.000 2 NULL NULL
2009-01-22 03:33:00.000 0 9,9 9,9

i Try to to calculate the average of two column
But here is the problem.

When i dont get an value of one of them
i dont get an result for the second column.
But i really need that .

So the result would more looks like

2009-01-22 02:23:00.000 0 9,9 9,9
2009-01-22 02:43:00.000 NULL 9,9 9,9
2009-01-22 02:53:00.000 10 9,9 9,95
2009-01-22 03:03:00.000 NULL NULL NULL
2009-01-22 03:13:00.000 NULL NULL NULL
2009-01-22 03:23:00.000 2 NULL 2
2009-01-22 03:33:00.000 0 9,9 9,9

How to achieve that? has any one an idea?

I simple from the beginning use (K1 + K2) / 2 but
that couldn't be right if there is som NULL value either.

maby to use WHEN or CASE ?

Most greatful

/Voy

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:34:14

isnull(K1,0) + isnull(K2,0) / 2

No, you're never too old to Yak'n'Roll if you're too young to die.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-03 : 07:35:34

quote:
Originally posted by webfred

isnull(K1,0) + isnull(K2,0) / 2

No, you're never too old to Yak'n'Roll if you're too young to die.

More accurately

(isnull(K1,0) + isnull(K2,0)) / 2.0

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-03 : 07:39:40

quote:
Originally posted by madhivanan

quote:
Originally posted by webfred

isnull(K1,0) + isnull(K2,0) / 2

No, you're never too old to Yak'n'Roll if you're too young to die.

More accurately

(isnull(K1,0) + isnull(K2,0)) / 2.0

Madhivanan

Failing to plan is Planning to fail

OK - but in this case I feared your answer comes faster than mine - so I missed it.

Just kidding

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 07:57:17

this ?


(isnull(nullif(K1, 0), K2) + isnull(nullif(K2, 0),K1)) / 2

KH
[spoiler]Time is always against us[/spoiler]

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-11-03 : 08:01:03

whow, that was quick!

Thanks guys you are terrific.

For me it feels little embarrassing that i couldn't figured it out by my self. My solutions was getting to long!

Well thats why this forum exists i guess.

What works was this:

(ISNULL(K1,0) + ISNULL(K2,0)) / 2

Nothing more, and nothing less. simple as pie

Thanks!

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-11-03 : 08:17:40

This was greate guys

now i have one more

How do i divide with the right number of columns that exists?

let assume that we early had 2 all the time.
But that gives one type of average.
If i want to calculate with the right number
how could that be possible?

for example

(NULL + 2 ) / 2 would give with your solution 1

but for me i would more want

(NULL + 2 ) / 1 that gives med 2

i started something like this

(ISNULL([k1],0) + ISNULL([k1],0)) /
(
CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END
)
But it doesn't like to divide with zero :)

Let see if you are quick on this one to ;)

thanks again!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 08:20:02

try the solution that i posted

KH
[spoiler]Time is always against us[/spoiler]

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-11-03 : 08:27:33

Nice that work.

But im not sure how to do if i want to upscale that combinations for example let say 4 columns or so.

How do i use your great solutions for 4 columns from K1 to K4 ?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 08:37:00

write a UDF to do the avg for 4 columns


declare @K1 decimal(10,2), @K2 decimal(10,2), @K3 decimal(10,2), @K4 decimal(10,2)

select avg(K)
from   (select @K1 as K union all select @K2 union all select @K3 union all select @K4) k

KH
[spoiler]Time is always against us[/spoiler]

voyager838
Yak Posting Veteran

90 Posts

Posted - 2009-11-03 : 08:59:38

Thank you khtan

Your solution is nice.

I have my own now. not elegant as yours, but it works
and no need for UDF

But now i have to see which one that is most effective.

this is my solution:
SELECT [LogTime]

(ISNULL([k1],0) + ISNULL([k2],0)) /
CASE WHEN (
(
CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN [k2] IS NOT NULL THEN 1 ELSE 0 END
) > 0 )
THEN
(
CASE WHEN [k1] IS NOT NULL THEN 1 ELSE 0 END +
CASE WHEN [k2] IS NOT NULL THEN 1 ELSE 0 END
)
ELSE 1 END
FROM dbo.[Table]

This avoids the division with zero and gives the right result.
this could i guess be more efficient, but its looks simple.

Thanks again!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-03 : 09:28:34

another way for the 2 column avg


coalesce(( nullif(K1, 0) + nullif(K2, 0) ) / 2, nullif(K1, 0), nullif(K2, 0))

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources