AVG() aggregate with CASE

i have 2 columns of type decimal that are divided and rounded and
return zero if the divisor is zero. it all works fine until i try to
AVG() them. now it seems my CASE to avoid dividing by zero is no
longer working...or i'm just approaching it all the wrong way. my
brain is a bit fried from staying up late to meet a deadline and i was
wondering if someone could give some advice.
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
b.Value1 = 101664
a.Value2 = 7730
Rating = 1.31
i now try putting the AVG() function to work like this:
SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
* 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
b.ID GROUP BY a.Value2
if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
get one record with the Rating = 1.07. yay!
the problem comes in where sometimes, the Values are 0. before using
the AVG() function, the CASE took care of these columns so that my
Rating results would be 0. but now i keep getting a "Divide by zero
error encountered". if the CASE is working, it shouldn't even try the
AVG() calculation right? or am i approaching this the wrong way? and
no, i can't use the SET ANSI_WARNINGS OFF because we're not using
stored procedures in this case.
thanks for any input or insight you may provide!
susieWhy not avoid the 0 problem by putting in a WHERE clause? ie
WHERE value1 > 0
AND value2 > 0
If that's not going to work, post some more sample values for tables a and b
.
Thanks
Damien
"TuBuGuRL" wrote:

> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>|||hi damien,
thanks for the reply. the WHERE clause is a good idea, but i don't
want to exclude these values, i need them to show up as averaged 0 in
my report.
i just realized that in my post above it's supposed to say "... ROUND(
(b.Value1 / a.Value2) ) ..." NOT "... ROUND( (b.Value1 / b.Value2) )
..."
anyway more sample data:
a.Value2 = 5910
b.Value1 = 120779
Rating = 2.04
5 different Ratings: 2.04, 1.01, 0.98, 0.91, 0.41
Avg() rating = 1.07
data used when i receive the error:
a.Value2 = 0
b.Value2 = 0
Rating = .0000000000000000
5 different Ratings: all .0000000000000000
shouldn't the CASE i use in the SELECT avoid even trying to AVG() the 0
Ratings?
thanks,
susie|||Put the CASE inside the AVG
...
AVG(CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND(...) END)
...
TuBuGuRL wrote:
> i have 2 columns of type decimal that are divided and rounded and
> return zero if the divisor is zero. it all works fine until i try to
> AVG() them. now it seems my CASE to avoid dividing by zero is no
> longer working...or i'm just approaching it all the wrong way. my
> brain is a bit fried from staying up late to meet a deadline and i was
> wondering if someone could give some advice.
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE ROUND((b.Value1 / b.Value2 *
> 1000) * 100, 2, 1) END AS Rating FROM a INNER JOIN b ON a.ID = b.ID
> b.Value1 = 101664
> a.Value2 = 7730
> Rating = 1.31
> i now try putting the AVG() function to work like this:
> SELECT CASE a.Value2 WHEN 0 THEN 0 ELSE AVG( ROUND((b.Value1 / b.Value2
> * 1000) * 100, 2, 1) ) END AS Rating FROM a INNER JOIN b ON a.ID =
> b.ID GROUP BY a.Value2
> if i have 5 rows of results .96, .97, .98, 1.13, 1.31, then it works i
> get one record with the Rating = 1.07. yay!
> the problem comes in where sometimes, the Values are 0. before using
> the AVG() function, the CASE took care of these columns so that my
> Rating results would be 0. but now i keep getting a "Divide by zero
> error encountered". if the CASE is working, it shouldn't even try the
> AVG() calculation right? or am i approaching this the wrong way? and
> no, i can't use the SET ANSI_WARNINGS OFF because we're not using
> stored procedures in this case.
> thanks for any input or insight you may provide!
> susie
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
10
10/3 = 3.33 but the result is 3
SELECT AVG(CAST(AmpReading AS REAL)) ...
David Portas
SQL Server MVP
|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:

> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG of integer column returns integers result - newbie

I am averaging integer columns, the result is integer not real or decimal
How do I get aroung this?
SELECT AVG(AmpReading)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
Values are
1
3
6
--
10
10/3 = 3.33 but the result is 3SELECT AVG(CAST(AmpReading AS REAL)) ...
--
David Portas
SQL Server MVP
--|||Try,
SELECT AVG(AmpReading * 1.00)
FROM tblChillWaterSystems
GROUP BY LocationID, SystemID
or cast [AmpReading] to numeric.
AMB
"Craig" wrote:
> I am averaging integer columns, the result is integer not real or decimal
> How do I get aroung this?
> SELECT AVG(AmpReading)
> FROM tblChillWaterSystems
> GROUP BY LocationID, SystemID
> Values are
> 1
> 3
> 6
> --
> 10
> 10/3 = 3.33 but the result is 3
>
>

AVG for a Column

Hai All,

I have a problem in creating AVG for a Column.
Cos i have a Column ,that is formulated from Division of two other columns Summation
(i.e) SUM(COL1)/SUM(COL2) = COL3
I need to create AVG for COL3
It's not possible to use like this : AVG( SUM(COL1)/SUM(COL2) )
So i used Function to get the Output.
Is there any other way to formulate this work.
Please Enlighten me.
Thanx,
Karthik.A

You could try a derived table:

SELECT AVG(x) AS theAverage
FROM
(
SELECT
SomeCol,
SUM(ColA) / SUM(ColB) AS x
FROM Tbl
GROUP BY SomeCol
) y (SomeCol, x)

|||Hai Adam,
I am already have the data values come from a Stored Procedure.
What i need is,for COL3=AVG(Sum(COL1)/Sum(COL2))
Already some table columns of theStored Procedure are assign to the COL1 and COL2.
Do u need any more explanation.
Thanx,
Karthik.A|||Yes; can you post DDL, some sample data, and the code you're trying to use right now?
See: Etiquette [ASP FAQ]

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:

Averaging a set of columns in the same table

I have a table which has 100 columns with appx 20000 rows
Each column represents a set of data from a different source.
Anywhere from 1 to 100 of the columns may contain data (empty will be null)
I need to find a way to average accross the rows depending on the number of
rows which have data
Ie for first row
(Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
or
(Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
Note number of rows will always be the same for every column appx 20000
I know I could convert the horizontal data into vertical but this will mean
a table of over 2million rows. Other constarints force me to aviod this.
Any ideas ?
Hi
Normalize the table and then you can do averages over the groups (as you now
have rows) using the group by clause.
Your table design is not conducive to easy programming.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>I have a table which has 100 columns with appx 20000 rows
> Each column represents a set of data from a different source.
> Anywhere from 1 to 100 of the columns may contain data (empty will be
> null)
> I need to find a way to average accross the rows depending on the number
> of
> rows which have data
> Ie for first row
> (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> or
> (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
>
> Note number of rows will always be the same for every column appx 20000
> I know I could convert the horizontal data into vertical but this will
> mean
> a table of over 2million rows. Other constarints force me to aviod this.
> Any ideas ?
|||> I know I could convert the horizontal data into vertical but this will mean
> a table of over 2million rows.
And... ?

> Other constarints force me to aviod this.
What constraints? This design is likely to be an awful nightmare and
performance bottleneck. The cost of supporting it is surely far greater
than that of fixing it.
but ...
(COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0 ) ...)/
(CASE WHEN col1 IS NOT NULL THEN 1 END +
CASE WHEN col2 IS NOT NULL THEN 1 END +
CASE WHEN col3 IS NOT NULL THEN 1 END +
...)
(yuck!)
David Portas
SQL Server MVP
|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Normalize the table and then you can do averages over the groups (as you now
> have rows) using the group by clause.
> Your table design is not conducive to easy programming.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
> news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>
>
|||Thanks David. I quite agree with your comments. Only you know how it can be,
small machines with small resources run by people with small brains and ears.
Now I have your and Mikes opinions I can get this issue fixed the proper
way...
Appreciate your time.
Regards
"David Portas" wrote:

> And... ?
>
> What constraints? This design is likely to be an awful nightmare and
> performance bottleneck. The cost of supporting it is surely far greater
> than that of fixing it.
> but ...
> (COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0 ) ...)/
> (CASE WHEN col1 IS NOT NULL THEN 1 END +
> CASE WHEN col2 IS NOT NULL THEN 1 END +
> CASE WHEN col3 IS NOT NULL THEN 1 END +
> ...)
> (yuck!)
> --
> David Portas
> SQL Server MVP
> --
>

Averaging a set of columns in the same table

I have a table which has 100 columns with appx 20000 rows
Each column represents a set of data from a different source.
Anywhere from 1 to 100 of the columns may contain data (empty will be null)
I need to find a way to average accross the rows depending on the number of
rows which have data
Ie for first row
(Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
or
(Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
Note number of rows will always be the same for every column appx 20000
I know I could convert the horizontal data into vertical but this will mean
a table of over 2million rows. Other constarints force me to aviod this.
Any ideas ?Hi
Normalize the table and then you can do averages over the groups (as you now
have rows) using the group by clause.
Your table design is not conducive to easy programming.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>I have a table which has 100 columns with appx 20000 rows
> Each column represents a set of data from a different source.
> Anywhere from 1 to 100 of the columns may contain data (empty will be
> null)
> I need to find a way to average accross the rows depending on the number
> of
> rows which have data
> Ie for first row
> (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> or
> (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
>
> Note number of rows will always be the same for every column appx 20000
> I know I could convert the horizontal data into vertical but this will
> mean
> a table of over 2million rows. Other constarints force me to aviod this.
> Any ideas ?|||> I know I could convert the horizontal data into vertical but this will mean">
> a table of over 2million rows.
And... ?

> Other constarints force me to aviod this.
What constraints? This design is likely to be an awful nightmare and
performance bottleneck. The cost of supporting it is surely far greater
than that of fixing it.
but ...
(COALESCE(col1,0)+COALESCE(col2,0)+COALE
SCE(col3,0) ...)/
(CASE WHEN col1 IS NOT NULL THEN 1 END +
CASE WHEN col2 IS NOT NULL THEN 1 END +
CASE WHEN col3 IS NOT NULL THEN 1 END +
..)
(yuck!)
David Portas
SQL Server MVP
--|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Normalize the table and then you can do averages over the groups (as you n
ow
> have rows) using the group by clause.
> Your table design is not conducive to easy programming.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
> news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>
>|||Thanks David. I quite agree with your comments. Only you know how it can be
,
small machines with small resources run by people with small brains and ears
.
Now I have your and Mikes opinions I can get this issue fixed the proper
way...
Appreciate your time.
Regards
"David Portas" wrote:

> And... ?
>
> What constraints? This design is likely to be an awful nightmare and
> performance bottleneck. The cost of supporting it is surely far greater
> than that of fixing it.
> but ...
> (COALESCE(col1,0)+COALESCE(col2,0)+COALE
SCE(col3,0) ...)/
> (CASE WHEN col1 IS NOT NULL THEN 1 END +
> CASE WHEN col2 IS NOT NULL THEN 1 END +
> CASE WHEN col3 IS NOT NULL THEN 1 END +
> ...)
> (yuck!)
> --
> David Portas
> SQL Server MVP
> --
>

Averaging a set of columns in the same table

I have a table which has 100 columns with appx 20000 rows
Each column represents a set of data from a different source.
Anywhere from 1 to 100 of the columns may contain data (empty will be null)
I need to find a way to average accross the rows depending on the number of
rows which have data
Ie for first row
(Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
or
(Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
Note number of rows will always be the same for every column appx 20000
I know I could convert the horizontal data into vertical but this will mean
a table of over 2million rows. Other constarints force me to aviod this.
Any ideas ?Hi
Normalize the table and then you can do averages over the groups (as you now
have rows) using the group by clause.
Your table design is not conducive to easy programming.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
>I have a table which has 100 columns with appx 20000 rows
> Each column represents a set of data from a different source.
> Anywhere from 1 to 100 of the columns may contain data (empty will be
> null)
> I need to find a way to average accross the rows depending on the number
> of
> rows which have data
> Ie for first row
> (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> or
> (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
>
> Note number of rows will always be the same for every column appx 20000
> I know I could convert the horizontal data into vertical but this will
> mean
> a table of over 2million rows. Other constarints force me to aviod this.
> Any ideas ?|||> I know I could convert the horizontal data into vertical but this will mean
> a table of over 2million rows.
And... ?
> Other constarints force me to aviod this.
What constraints? This design is likely to be an awful nightmare and
performance bottleneck. The cost of supporting it is surely far greater
than that of fixing it.
but ...
(COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0) ...)/
(CASE WHEN col1 IS NOT NULL THEN 1 END +
CASE WHEN col2 IS NOT NULL THEN 1 END +
CASE WHEN col3 IS NOT NULL THEN 1 END +
...)
(yuck!)
--
David Portas
SQL Server MVP
--|||Thanks Mike.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Normalize the table and then you can do averages over the groups (as you now
> have rows) using the group by clause.
> Your table design is not conducive to easy programming.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "Jinx1966" <Jinx1966@.discussions.microsoft.com> wrote in message
> news:D9C74420-C0A1-400B-8518-3CE1BE0B19D5@.microsoft.com...
> >I have a table which has 100 columns with appx 20000 rows
> > Each column represents a set of data from a different source.
> > Anywhere from 1 to 100 of the columns may contain data (empty will be
> > null)
> >
> > I need to find a way to average accross the rows depending on the number
> > of
> > rows which have data
> >
> > Ie for first row
> > (Col1 + Col2 + Ccol3...Col34) / 34 where only col1 to col34 have data.
> > or
> > (Col1 + Col2 + Ccol3) / 3 where only col1 to col3 have data.
> >
> >
> > Note number of rows will always be the same for every column appx 20000
> >
> > I know I could convert the horizontal data into vertical but this will
> > mean
> > a table of over 2million rows. Other constarints force me to aviod this.
> >
> > Any ideas ?
>
>|||Thanks David. I quite agree with your comments. Only you know how it can be,
small machines with small resources run by people with small brains and ears.
Now I have your and Mikes opinions I can get this issue fixed the proper
way...
Appreciate your time.
Regards
"David Portas" wrote:
> > I know I could convert the horizontal data into vertical but this will mean
> > a table of over 2million rows.
> And... ?
> > Other constarints force me to aviod this.
> What constraints? This design is likely to be an awful nightmare and
> performance bottleneck. The cost of supporting it is surely far greater
> than that of fixing it.
> but ...
> (COALESCE(col1,0)+COALESCE(col2,0)+COALESCE(col3,0) ...)/
> (CASE WHEN col1 IS NOT NULL THEN 1 END +
> CASE WHEN col2 IS NOT NULL THEN 1 END +
> CASE WHEN col3 IS NOT NULL THEN 1 END +
> ...)
> (yuck!)
> --
> David Portas
> SQL Server MVP
> --
>

Average Expression ?

Hi,
I want to calculate the Average Sales for given columns, eg
Jan Fab Mar April AVERAGE
100 100 100 100 100
20 20 10 15.66
30 10 50 30
Is it possible using an Expression ?
ThanksCreate a matrix report and change the aggregate from Sum to avg
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Vishal" wrote:
> Hi,
> I want to calculate the Average Sales for given columns, eg
> Jan Fab Mar April AVERAGE
> 100 100 100 100 100
> 20 20 10 15.66
> 30 10 50 30
> Is it possible using an Expression ?
> Thanks
>
>

Avegage from 4 columns

I have 4 columns and i need average from this columns.
I can sum values from this columns and divide by 4, but this is inproper in case of nulls.

Is there any easy way to do that?

Right now I'am using:

---

((SELECT CASE WHEN Column1 IS NOT null
OR Column2 IS NOT NULL
OR Column3 IS NOT NULL
OR Column4 IS NOT NULL THEN
(SELECT
(CASE WHEN Column1 IS NOT null THEN Column1 ELSE 0 END +
CASE WHEN Column2 IS NOT null THEN Column2 ELSE 0 END +
CASE WHEN Column3 IS NOT null THEN Column3 ELSE 0 END +
CASE WHEN Column4 IS NOT null THEN Column4 ELSE 0 END)
/ (0.0 +
CASE WHEN Column1 IS NOT null THEN 1 ELSE 0 END +
CASE WHEN Column2 IS NOT null THEN 1 ELSE 0 END +
CASE WHEN Column3 IS NOT null THEN 1 ELSE 0 END +
CASE WHEN Column4 IS NOT null THEN 1 ELSE 0 END))
ELSE
null
END

---

Can you post a small sample with some numbers demonstrating your problem, along with the desired output and also an explanation with rules of exactly how you want your averages calculated?

/Kenneth

|||

This is probably the best way to do it. It is straightforward and easy to debug, if a pain to write.

It is never going to be easy when working with a vector of values like this in SQL. SQL is optimized solely for working with sets of data, so if you had another table with this value in it and a key to group on, you could have a million values and it would work. The first normal form deals with this situation of having a variable number of values in a row for this very reason.

This kind of thing isn't always wrong of course, I have a place where I have to compare date values from a join to see when any values in the row were last updated. It is a big red flag though.

|||

You can use some other syntax to shorten the notation, but you will have to do essentially the same logic. If you have the choice to reorganize you tables, you might want to put the four columns in a separate table. That would allow you to use the Aggregate functions, which already have the NULL handling.

Here is an example that shows how to use other functions to shorten the notation. In order to prevent a divide by zero in the case where all 4 columns are null. I chose to return 0, but you could also return null.

drop table #colAvg

create table #colAvg(

pkid int not null Identity(1,1),

colA float null,

colB float null,

colC float null,

colD float null

)

insert #colAvg values( 1.0, null, 3.0, 4.0 )

insert #colAvg values( null, null, 3.0, 4.0 )

insert #colAvg values( 1.0, null, null, 4.0 )

insert #colAvg values( null, null, null, 4.0 )

insert #colAvg values( null, null, null, null )

Selectpkid,

numerator = (IsNull( colA, 0 ) + IsNull( colB, 0 ) + IsNull( colC, 0 ) + IsNull( colD, 0 )),

denominator = IsNull( NullIf( IsNull( Sign( colA ), 0 ) + IsNull( Sign( colB), 0 ) + IsNull( Sign( colC ), 0 ) + IsNull( Sign( colD ), 0 ), 0 ), 1 ),

average = (IsNull( colA, 0 ) + IsNull( colB, 0 ) + IsNull( colC, 0 ) + IsNull( colD, 0 )) /

IsNull( NullIf( IsNull( Sign( colA ), 0 ) + IsNull( Sign( colB), 0 ) + IsNull( Sign( colC ), 0 ) + IsNull( Sign( colD ), 0 ), 0 ), 1 )

From#colAvg

pkid numerator denominator average

-- -- -

1 8.0 3.0 2.6666666666666665

2 7.0 2.0 3.5

3 5.0 2.0 2.5

4 4.0 1.0 4.0

5 0.0 1.0 0.0|||table (Column1, Column2, Column3, Column4).
Table always have only 1 row.

Example1:
values (1,2,3,4) - average 2.5

Example2:
values(1,2,3,null) - average 2

Example3:
values(1,null,null,9) - average 5|||

Wow. Totally suggest you create 1-4 rows instead of 1-4 scalar values like this. I don't know what the statement used to load the data, but I will be changing it to insert into a table would be far easier in the long run (especially when the annoying user realizes that actually they need 10+ values)

For one row, you could do this (for > 1 row you would need a bit more in the where clause or a group on the outside)

select sum(columnValue)
from (select column1 as columnValue
from table
where column1 is not null
UNION ALL
select column2
from table
where column2 is not null
UNION ALL
select column3
from table
where column3 is not null
UNION ALL
select column4
from table
where column4 is not null) as values

|||

Another way to write the query is to do below. The cross join might be faster depending on the data.

select avg(case c.n

when 1 then t.column1

when 2 then t.column2

when 3 then t.column3

when 4 then t.column4

end)

from table as t

cross join (select 1 union all select 2 union all select 3 union all select 4) as c(n)

|||

Nice solution to a unnice problem :) I am always impressed by the many ways one can write SQL to get past improper design. It just goes to show you that a few minutes upfront can often save you hours later. If the data was in rows:

select sum(value)
from table

Oh well, certainly not as many creative solutions required :)

|||

select (isnull(column1,0)+isnull(column1,0)+isnull(column1,0)+isnull(column1,0))/4

FROM TABLEX

|||

I use the same temp table from anomolous :

For a unpivot solution for Sql Server 2005:

drop table #colAvg

create table #colAvg(

pkid int not null Identity(1,1),

colA float null,

colB float null,

colC float null,

colD float null

)

insert #colAvg values( 1.0, null, 3.0, 4.0 )

insert #colAvg values( null, null, 3.0, 4.0 )

insert #colAvg values( 1.0, null, null, 4.0 )

insert #colAvg values( null, null, null, 4.0 )

insert #colAvg values( null, null, null, null )

SELECT pkid, AVG(newValues) as avgFrom4Columns FROM

(SELECT pkid, colA, colB, colC, colD

FROM #colAvg) p

UNPIVOT

(newValues FOR cols4 IN (colA, colB, colC, colD)

) as unpvt

GROUP BY pkid

By the way, the Isnull(colnameX,0) solution does not provide the right answer .

|||Good use of the new unpivot operator. Btw, it does the same as the CROSS JOIN technique although easier to express in terms of syntax.|||Data are in columns not in rows.|||I can't divide by 4. I must count not null columns. I must divide by 4 or 3 or 2 or 1 (depends on values).|||

---

select sum(columnValue)
from (select column1 as columnValue
from table
where column1 is not null
UNION ALL
select column2
from table
where column2 is not null
UNION ALL
select column3
from table
where column3 is not null
UNION ALL
select column4
from table
where column4 is not null) as values

---

This is a second way I want to do this, but instead of "sum" you must use "avg" and you can remove "where" statement (avg function automaticaly removes null values).

Here is my another question. Witch way will be faster? Yours with "union" or my with "case"?|||Case approach is faster since the query using CASE expression does less I/O than UNION ALL approach or CROSS JOIN or UNPIVOT. You can perform the calculations in one pass on each row.

auto-summary columns

In a lot of cases for the sake of performance I use UPDATE, INSERT, and
DELETE triggers for maintaining a denormalized column in another table that
stores summary information (such as an inventory transaction table and a
total on-hand balance column in an item master table).
I was thinking, since I've made lots of variations of this all of which are
basically the same in form, it would be convenient for MS to supply a
special 'summary' column type that automatically monitors the other table's
column being summarized and stays up to date so I don't have to create
triggers every time. I know you can get summaries just by writing an SP or
view to retrieve them, but that's really inefficient when the table being
summarized gets large.
I'm posting this idea on the off chance I've missed a feature in SS2K that
does something like this already... also, does anyone know if this is a
feature known to be coming in Yukon?
TIA,
BobHi
Have you looked at Computed Columns?
BOL has info on it.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Bob" <noone@.nowhere.com> wrote in message
news:OHRHI$CIFHA.2136@.TK2MSFTNGP14.phx.gbl...
> In a lot of cases for the sake of performance I use UPDATE, INSERT, and
> DELETE triggers for maintaining a denormalized column in another table
that
> stores summary information (such as an inventory transaction table and a
> total on-hand balance column in an item master table).
> I was thinking, since I've made lots of variations of this all of which
are
> basically the same in form, it would be convenient for MS to supply a
> special 'summary' column type that automatically monitors the other
table's
> column being summarized and stays up to date so I don't have to create
> triggers every time. I know you can get summaries just by writing an SP or
> view to retrieve them, but that's really inefficient when the table being
> summarized gets large.
> I'm posting this idea on the off chance I've missed a feature in SS2K that
> does something like this already... also, does anyone know if this is a
> feature known to be coming in Yukon?
> TIA,
> Bob
>|||Have you considered using indexed views?
Always maintain a healthy degree of skepticism in the face of arguments
that favour denormalization "for the sake of performance".
Denormalization is a trade off. One query's performance is improved but
elsewhere performance and integrity suffers. Denormalization can also
be a slippery slope toward more denormalization. Certainly if you
denormalize "in a lot of cases" then I suggest you take a long hard
look at whether you have the correct design and implementation. There
are usually better solutions.
David Portas
SQL Server MVP
--|||Second that, You should only denormalize AFTER a performance problem has
surfaced in a normalized data structure, and then only after examining all
the other options. There's a;most always a way to improve performance in a
normalized database schema, using properly designed and optimized indices.
And even if that approach isn;t sufficient to deal with the problem,
denormalization applied to a fully normalized schema will always be more
effective and successful than denormalization done out of the gate.
"David Portas" wrote:

> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||>> I use UPDATE, INSERT, and DELETE triggers for maintaining a
denormalized column in another table that stores summary information
(such as an inventory transaction table and a
total on-hand balance column in an item master table). <<
The only reason to store summary infomation is that this is a data
warehouse, that is so big that the recomputation would be too
expensive. But the data is static in a DW.
I would stick with nice, portable and always correct VIEWs instead of
proprietary triggers that fire everytime the table is touched.|||The funny thing is that for the cost of the coding you could probably
upgrade hardware enough to avoid the whole denormalization thing :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109888063.340480.52940@.g14g2000cwa.googlegroups.com...
> Have you considered using indexed views?
> Always maintain a healthy degree of skepticism in the face of arguments
> that favour denormalization "for the sake of performance".
> Denormalization is a trade off. One query's performance is improved but
> elsewhere performance and integrity suffers. Denormalization can also
> be a slippery slope toward more denormalization. Certainly if you
> denormalize "in a lot of cases" then I suggest you take a long hard
> look at whether you have the correct design and implementation. There
> are usually better solutions.
> --
> David Portas
> SQL Server MVP
> --
>|||All of my practical experience indicates to me that denormalization is a
good thing for storing aggregate information; I've seen it to be simple,
nothing but reliable, and dramatically increase read performance without
significantly impacting write performance.
As such, I don't find your argument compelling (partly because I'm not
concerned with portability). I'm clearly not one of such distinction and
experience as yourself; I would ask you to elaborate your point of view so I
can better understand it.
Bob
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1109898958.939621.87460@.o13g2000cwo.googlegroups.com...
> denormalized column in another table that stores summary information
> (such as an inventory transaction table and a
> total on-hand balance column in an item master table). <<
> The only reason to store summary infomation is that this is a data
> warehouse, that is so big that the recomputation would be too
> expensive. But the data is static in a DW.
> I would stick with nice, portable and always correct VIEWs instead of
> proprietary triggers that fire everytime the table is touched.
>|||>> All of my practical experience indicates to me that denormalization
is a good thing for storing aggregate information; I've seen it to be
simple, nothing but reliable, and dramatically increase read
performance without significantly impacting write performance. <<
I have seen the opposite. Triggers constantly firing slow things down.
The time to write a value is orders of magnitude greater than the time
to compute it. The extra storage starts to add up. Etc. But more
than that, data integrity gets shot in the foot. Example: Orders carry
the total in a column that is supposed to equal the sum of the order
details. I put a trigger on OrderDetails to modify Orders. But I have
no trigger on Orders, so someone can change that total directly -- and
they will. As I start to use only triggers for data integrity, I find
that more and more business rules need more than one trigger apiece.
That is a little hard in SQL Server and a serious problem in more
powerful SQL products that have BEFORE and AFTER, as well as multiple
trigger options.
not concerned with portability). I'm clearly not one of such
distinction and experience as yourself; I would ask you to elaborate
your point of view so I can better understand it. <<
Portability and standard code are always issues. You port from one
release of the same software to another. You hire programmers who do
not know your local dialect. Unless the company business plan is to
stagnate and die, you will port and maintain code -- this is 80% of the
total cost of a system over its lifetime. Pros write code for other
people and amateurs write code to amuse themselves.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
> <...>
Points taken, thank you.

> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
I have no problem telling my customers, 'Microsoft only'. So far I've
received nothing but nods of approval. I will port and maintain code, but
only to other MS products.
I know, I'm going to hell...
Bob|||I have never found anything I couldn't do using Microsoft's triggers. I
agree completely that keeping summary data is usually wrong, and I have only
had one case where it was necessary. We had a manufacturing application
that calculated stuff that took the last fifty readings and the last fifty
calculated values into consideration (one SQL Statement was 200+lines.)
Needless to say that it took way too long to recalculate these values on
demand. So we had a trigger call the summary procedure when values were
entered.
Either way, it is always my advice to never denormalize your data for
performance until you have exhausted all of the usual tips. Indexing, views
(indexed too,) correct hardware, well built apps, etc.first. If it is
needed, it is needed, but seldom is that true.
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Blog - http://spaces.msn.com/members/drsql/
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1110035642.897731.285480@.z14g2000cwz.googlegroups.com...
> is a good thing for storing aggregate information; I've seen it to be
> simple, nothing but reliable, and dramatically increase read
> performance without significantly impacting write performance. <<
> I have seen the opposite. Triggers constantly firing slow things down.
> The time to write a value is orders of magnitude greater than the time
> to compute it. The extra storage starts to add up. Etc. But more
> than that, data integrity gets shot in the foot. Example: Orders carry
> the total in a column that is supposed to equal the sum of the order
> details. I put a trigger on OrderDetails to modify Orders. But I have
> no trigger on Orders, so someone can change that total directly -- and
> they will. As I start to use only triggers for data integrity, I find
> that more and more business rules need more than one trigger apiece.
> That is a little hard in SQL Server and a serious problem in more
> powerful SQL products that have BEFORE and AFTER, as well as multiple
> trigger options.
>
> not concerned with portability). I'm clearly not one of such
> distinction and experience as yourself; I would ask you to elaborate
> your point of view so I can better understand it. <<
> Portability and standard code are always issues. You port from one
> release of the same software to another. You hire programmers who do
> not know your local dialect. Unless the company business plan is to
> stagnate and die, you will port and maintain code -- this is 80% of the
> total cost of a system over its lifetime. Pros write code for other
> people and amateurs write code to amuse themselves.
>