AVG by columns not by rows

Hello is there a way to calculate Average values for columns not for
rows.
E.g.
col1 col2 avg
--
2 2 2.0
3 2 2.5
I found something that requires manual sum of every column and than
dividing with number of nonzero an nonnull columns. I ended using tons
of case when... statements.
Is there any simple solution?Assuming you have a PK on your table
SELECT PK, AVG(AvgCol)
FROM
(SELECT PK, C1 AS AvgCol
FROM YourTable
UNION
SELECT PK, C2
FROM YourTable) T
GROUP BY PK
Regards
Roji. P. Thomas
http://toponewithties.blogspot.com
<milan.letic@.gmail.com> wrote in message
news:1151930480.771154.61470@.v61g2000cwv.googlegroups.com...
> Hello is there a way to calculate Average values for columns not for
> rows.
> E.g.
> col1 col2 avg
> --
> 2 2 2.0
> 3 2 2.5
> I found something that requires manual sum of every column and than
> dividing with number of nonzero an nonnull columns. I ended using tons
> of case when... statements.
> Is there any simple solution?
>|||If you need the entire original row, as I suspect you will, you will
need to join this back to the orignal table:
SELECT T.KeyCol,
AvgX = AVG(CASE WHEN N.X = 1 AND T.col1 <> 0 THEN T.col1
WHEN N.X = 2 AND T.col2 <> 0 THEN T.col2
WHEN N.X = 3 AND T.col3 <> 0 THEN T.col3
WHEN N.X = 4 AND T.col4 <> 0 THEN T.col4
WHEN N.X = 5 AND T.col5 <> 0 THEN T.col5
END)
FROM TheTable as T,
(select 1 as X UNION ALL
select 2 UNION ALL
select 3 UNION ALL
select 4 UNION ALL
select 5) as N
GROUP BY T.KeyCol
Also, your sample data appears to start with integer values but the
average is to one decimal place, so it appears some conversion before
the AVG is applied will be required.
Roy Harvey
Beacon Falls, CT
On 3 Jul 2006 05:41:20 -0700, milan.letic@.gmail.com wrote:

>Hello is there a way to calculate Average values for columns not for
>rows.
>E.g.
>col1 col2 avg
>--
>2 2 2.0
>3 2 2.5
>I found something that requires manual sum of every column and than
>dividing with number of nonzero an nonnull columns. I ended using tons
>of case when... statements.
>Is there any simple solution?|||If your table has a primary key, you can unpivot the result and group by the
primary key and then join the result to the original table.
create table dbo.t1 (
pk int not null identity primary key,
c1 int,
c2 int
)
go
insert into dbo.t1(c1, c2) values(2, 2)
insert into dbo.t1(c1, c2) values(3, 2)
go
select
t1.pk,
t1.c1,
t1.c2,
t2.col_avg
from
dbo.t1
inner join
(
select
a.pk,
avg(cast(
case b.c1
when 1 then a.c1
when 2 then a.c2
end as numeric(5, 2))) as col_avg
from
dbo.t1 as a
cross join
(select 1 as c1 union all select 2) as b
group by
a.pk
) as t2
on t1.pk = t2.pk
go
drop table dbo.t1
go
AMB
"milan.letic@.gmail.com" wrote:

> Hello is there a way to calculate Average values for columns not for
> rows.
> E.g.
> col1 col2 avg
> --
> 2 2 2.0
> 3 2 2.5
> I found something that requires manual sum of every column and than
> dividing with number of nonzero an nonnull columns. I ended using tons
> of case when... statements.
> Is there any simple solution?
>|||Guys, thank you. But, I don't understand your code. I don't know what
it does, which is more efficient.. I'm afraid I'll have to use my old
version.
Alejandro Mesa wrote:
> If your table has a primary key, you can unpivot the result and group by t
he
> primary key and then join the result to the original table.
> create table dbo.t1 (
> pk int not null identity primary key,
> c1 int,
> c2 int
> )
> go
> insert into dbo.t1(c1, c2) values(2, 2)
> insert into dbo.t1(c1, c2) values(3, 2)
> go
> select
> t1.pk,
> t1.c1,
> t1.c2,
> t2.col_avg
> from
> dbo.t1
> inner join
> (
> select
> a.pk,
> avg(cast(
> case b.c1
> when 1 then a.c1
> when 2 then a.c2
> end as numeric(5, 2))) as col_avg
> from
> dbo.t1 as a
> cross join
> (select 1 as c1 union all select 2) as b
> group by
> a.pk
> ) as t2
> on t1.pk = t2.pk
> go
> drop table dbo.t1
> go
>
> AMB
> "milan.letic@.gmail.com" wrote:
>|||Hi There,
Roji has already given you a solution , I had changed it minorly by
using UNION ALL instead of UNION and added where clause although it is
not required as much.
Here R is a value that uniquely identifies a row (i.e Primary key )
Create view tmpData3
as
Select 1 R,null a,2 b, 3 c
Union
Select 2 R,1 a,null b, 3 c
Union
Select 3 R,1 a,2 b, null c
Union
Select 4 R,1 a,null b, null c
Go
Select R,avg(col) From
(
Select R,cast(a as numeric) col from tmpData3 where a>0
Union All
Select R,b from tmpData3 where b>0
Union All
Select R,c from tmpData3 where c>0
)X group by R
drop view tmpData3
With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com
milan.letic@.gmail.com wrote:
> Guys, thank you. But, I don't understand your code. I don't know what
> it does, which is more efficient.. I'm afraid I'll have to use my old
> version.
>
> Alejandro Mesa wrote: