Doubt in AVG - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Doubt in AVG

Author

Topic

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 04:41:19

Hi

I have one Table with 2 columns as below
declare @TempTable Table(segment int,col1 numeric(8,3))
insert into @TempTable
select 0,1 union
select 0,3 union
select 0,5 union
select 0,1 union
select 1,7 union
select 1,9 union
select 1,10 union
select 1,20 union
select 1,10
select avg(col1) As FullAvg from @TempTable
Output
-------
7.85

select avg(col1) from @TempTable where segment =0
Output
-------
3.00
select avg(col1) from @TempTable where segment =1
Output
-------
11.50

If we have 2 avg values, like above then how can we get from that 2 avg values as full avg value.

from 3.00 and 11.50 values i have to get overall avg value.
Thanks
Ganesh

Solutions are easy. Understanding the problem, now, that's the hard part

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-07-08 : 04:52:06

avg * count = avg1 * count1 + avg2 * count2
something like that?

and using union all will be less confusing :)

Bjoern

(54224 row(s) affected)

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-08 : 04:53:24

[code]SELECT AVG(col1),
AVG(CASE WHEN segment =0 THEN col1 ELSE 0 END),
AVG(CASE WHEN segment =1 THEN col1 ELSE 0 END)
FROM @TempTable [/code]

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 05:02:02

Thanks for your reply, And how can we do the same thing if we have another grouping column like below
declare @TempTable Table(segment int,timeInMin int,col1 numeric(8,3))
insert into @TempTable
select 0,1,1 union
select 0,1,3 union
select 0,2,5 union
select 0,2,1 union
select 1,1,7 union
select 1,1,9 union
select 1,1,10 union
select 1,2,20 union
select 1,3,10

I have to get same avg value from this..

Thanks
Ganesh

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 05:13:23

Group by timeInMin ?

SELECT timeInMin, AVG(col1),
       AVG(CASE WHEN segment =0 THEN col1 ELSE 0 END),
       AVG(CASE WHEN segment =1 THEN col1 ELSE 0 END)
FROM  @TempTable
GROUP BY timeInMin

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

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-07-08 : 05:21:11

I'm surprised that Visakh's solution works. Isn't avg(1, 3, 5, 0, 0, 0, 0) supposed to be 8/7 ?

ganeshkumar08
Posting Yak Master

187 Posts

Posted - 2008-07-08 : 05:21:13

Thank you so much....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-07-08 : 05:28:09

quote:
Originally posted by bjoerns

I'm surprised that Visakh's solution works. Isn't avg(1, 3, 5, 0, 0, 0, 0) supposed to be 8/7 ?

You are right


SELECT timeInMin, AVG(col1),
       AVG(CASE WHEN segment =0 THEN col1 ELSE 0 END),
       AVG(CASE WHEN segment =1 THEN col1 ELSE 0 END)
FROM  @TempTable
GROUP BY timeInMin

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources