Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate

|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

Avg, Min & Max of two date fields in a Table

i have a table T_EPISODE that has two fields referral date (D_REF) and assessment date (A_DATE)

i need to get the average, min & max time between the referral and assessment dates where the assessment date falls within a given period (dteFromDate & dteToDate) and where assessment date is not null

but im a little bit stuck on how to do it. i know about the MIN & MAX functions but they only work one field at a time. do i need to do a cursor to get all the date differences then work off these for MIN, MAX & AVG?

any help would be greatly appreciated.

Cheers,
Craig

You take min, max, avg of a expression. An expression can be a column or anything else that qualifies as an expression.
select min(A_DATE - D_REF) from T_EPISODE
where A_DATE between dteFromDate and dteToDate|||

Hi andreas,

did you have any idea how to solve this problem for me?

i need to use the data to get the highest average mark?

now i have a table that contain the score for some question.

i need to group it into daily basic to get each day average score. and then i want to get the highest score over several day. anyway can help me to do it?

regards

terence chua

|||

Hallo,

created a small scenario to hopefully show how to do what you seem to want to do.

CREATE TABLE [dbo].[Scores](
[nScore] [int] NOT NULL,
[dtDate] [datetime] NOT NULL
) ON [PRIMARY]

I filled the table with some scores for various dates, I used a date in this month.

To get the average for a period in time I used this query
select avg(nScore) as 'Daily Average', dtDate
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'
group by dtDate
order by dtDate

where clause determines what period.

To get the highest score in a period
select max(nScore) as 'Top Score'
from dbo.Scores
where dtDate between '2006-01-01' and '2006-01-31'

I hope this helps, otherwise you might explain more detailed what you are trying to.

|||

Hi,

thank your help. i think you misunderstand my question. anyway i get my solution already. sorry to reply late. cos my company closing for CNY.

i not joining both function for avg() and max(). but i just use avg() to select the data i need. then i use the sort and filter in the object(table, chart, text box and so on). to select the top N record or bottom N record then i can get the maximun and minimun record already.

regards

terence chua

AVG using ROW_NUMBER

I'm using SQL Server 2005, sp 2. My query is below. What I want to see for the results is the average of all of partition 1, the average of partition 2, etc.Does anybody know how I can get this?

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

Results:

Partition This Year Date Code Location Code

1 .85 20070101 1

2 .58 20070509 1

1 .52 20070808 2

2 .54 20070905 2

3 .26 20070104 3

3 .26 20070905 3

Is this what you're looking for?

Code Snippet

select

Partition

,(sum(Thisyear)/count(*)) 'ThisYearAvg'

from

(

SELECT ROW_NUMBER() OVER (PARTITION BY Shop.Location_Code ORDER BY Shop.Date_Code) AS [PARTITION],

(Score) AS [This Year], Shop.Date_Code, Shop.Location_Code

FROM ETL.Transform_FactOpsMSScorecard SHOP INNER JOIN DW_DatamartDB.dbo.DimDate DD

ON Shop.Date_Code=DD.Date_Code

INNER JOIN DW_DatamartDB.dbo.DimLocation LOC ON

Shop.Location_Code = Loc.Location_Code

WHERE District_Code = (@.District)

) a

group by

Partition

|||

The average of what?

select

...,

avg(Score) over(partition by Shop.Location_Code) as avg_score

from

....

AMB

|||

Yes Anthony, That's exactly what I was looking for. Thank you so much for your help!

Lindsay

avg of timestamp

Is it possible to use AVG with timestamp?
I have a timestamp column with format 00:00:00, and I want to get the
average time of all rows for that column.
When I try it, I get this error:
The average aggregate operation cannot take a timestamp data type as an
argument.
Thanks!A timestamp is a point in time. So averaging is impossible.
This means that you would have to define the averaging formula yourself.
Let's assume all your datetime columns have the date 1900-01-01. In that
case you might use the formula:
SELECT
DATEADD(second,AVG(DATEDIFF(second,'19000101',MyDateColumn)),'19000101')
FROM MyTable
HTH,
Gert-Jan
chrismtoth@.gmail.com wrote:
> Is it possible to use AVG with timestamp?
> I have a timestamp column with format 00:00:00, and I want to get the
> average time of all rows for that column.
> When I try it, I get this error:
> The average aggregate operation cannot take a timestamp data type as an
> argument.
> Thanks!

AVG Function

I've imported several reports from Access that I am now trying to convert use
a shared SQL data source. Several of these report show an average value for a
datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
Does anyone have a workaround for RS to be able to average a time value on a
report. Thank youTry this:
=DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> I've imported several reports from Access that I am now trying to convert
use
> a shared SQL data source. Several of these report show an average value
for a
> datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> Does anyone have a workaround for RS to be able to average a time value on
a
> report. Thank you|||Thank you Robert,
This command returned an error "FromBinary is not a memvber of Date"
Any help is appreciated, thank you very much.
"Robert Bruckner [MSFT]" wrote:
> Try this:
> =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > I've imported several reports from Access that I am now trying to convert
> use
> > a shared SQL data source. Several of these report show an average value
> for a
> > datetime field in SQL, ex. a textbox with a value of =AVG(Fields.T1.Value)
> >
> > Does anyone have a workaround for RS to be able to average a time value on
> a
> > report. Thank you
>
>|||Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
Try this for RS 2000:
=new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"halej51" <halej51@.discussions.microsoft.com> wrote in message
news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> Thank you Robert,
> This command returned an error "FromBinary is not a memvber of Date"
> Any help is appreciated, thank you very much.
>
>
> "Robert Bruckner [MSFT]" wrote:
> > Try this:
> > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no
rights.
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > I've imported several reports from Access that I am now trying to
convert
> > use
> > > a shared SQL data source. Several of these report show an average
value
> > for a
> > > datetime field in SQL, ex. a textbox with a value of
=AVG(Fields.T1.Value)
> > >
> > > Does anyone have a workaround for RS to be able to average a time
value on
> > a
> > > report. Thank you
> >
> >
> >|||Robert,
That was a huge help! Thank you very much. I am getting a #Error in some of
the footer fields using this calculation but I think this might be a data
issue.
Thank you!
"Robert Bruckner [MSFT]" wrote:
> Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> Try this for RS 2000:
> =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "halej51" <halej51@.discussions.microsoft.com> wrote in message
> news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > Thank you Robert,
> >
> > This command returned an error "FromBinary is not a memvber of Date"
> >
> > Any help is appreciated, thank you very much.
> >
> >
> >
> >
> > "Robert Bruckner [MSFT]" wrote:
> >
> > > Try this:
> > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > >
> > > --
> > > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > >
> > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > I've imported several reports from Access that I am now trying to
> convert
> > > use
> > > > a shared SQL data source. Several of these report show an average
> value
> > > for a
> > > > datetime field in SQL, ex. a textbox with a value of
> =AVG(Fields.T1.Value)
> > > >
> > > > Does anyone have a workaround for RS to be able to average a time
> value on
> > > a
> > > > report. Thank you
> > >
> > >
> > >
>
>|||Another similar situation which help would be appreciated for.
I have a table with 4 datetime fields, storing only the time. In the report
I need to produce an average of these 4 fields like, (T1.Value + T2.Value +
T3.Value + T4.Value)/4. This obviously throws a similar error.
Any help is appreciated.
"halej51" wrote:
> Robert,
> That was a huge help! Thank you very much. I am getting a #Error in some of
> the footer fields using this calculation but I think this might be a data
> issue.
> Thank you!
>
> "Robert Bruckner [MSFT]" wrote:
> > Oh, the FromBinary method is only available on .NET 2.0 (and RS 2005).
> > Try this for RS 2000:
> > =new DateTime( Avg( CDate(Fields!T1.Value).Ticks ) )
> >
> > --
> > This posting is provided "AS IS" with no warranties, and confers no rights.
> >
> >
> > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > news:7629239C-2FDD-4049-84EA-8326EF7299CA@.microsoft.com...
> > > Thank you Robert,
> > >
> > > This command returned an error "FromBinary is not a memvber of Date"
> > >
> > > Any help is appreciated, thank you very much.
> > >
> > >
> > >
> > >
> > > "Robert Bruckner [MSFT]" wrote:
> > >
> > > > Try this:
> > > > =DateTime.FromBinary( Avg( CDate(Fields!T1.Value).Ticks ) )
> > > >
> > > > --
> > > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > > >
> > > > "halej51" <halej51@.discussions.microsoft.com> wrote in message
> > > > news:0A8D8368-EEC2-46E7-A24A-EB517AA4D8C4@.microsoft.com...
> > > > > I've imported several reports from Access that I am now trying to
> > convert
> > > > use
> > > > > a shared SQL data source. Several of these report show an average
> > value
> > > > for a
> > > > > datetime field in SQL, ex. a textbox with a value of
> > =AVG(Fields.T1.Value)
> > > > >
> > > > > Does anyone have a workaround for RS to be able to average a time
> > value on
> > > > a
> > > > > report. Thank you
> > > >
> > > >
> > > >
> >
> >
> >

Avg function

The following function returns an average in minutes between to dates.
=Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
Fields!TODO_ACTL_END_DT.Value ))/60
My problem is in figuring out how to truncate decimal places for example the
report returns 36.2795358649783 when all I want is 36.
If any one has any ideas that would be greatly appreciated.
--
kmatth007use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60,"0")
"kmatth007" wrote:
> The following function returns an average in minutes between to dates.
> =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> Fields!TODO_ACTL_END_DT.Value ))/60
> My problem is in figuring out how to truncate decimal places for example the
> report returns 36.2795358649783 when all I want is 36.
> If any one has any ideas that would be greatly appreciated.
> --
> kmatth007|||Thanks! This worked.
kmatth007
"ש×?×?×?" wrote:
> use format(Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60,"0")
> "kmatth007" wrote:
> > The following function returns an average in minutes between to dates.
> >
> > =Avg(Time.ClsReportUtils.getResponseTime(Fields!CREATED.Value,
> > Fields!TODO_ACTL_END_DT.Value ))/60
> >
> > My problem is in figuring out how to truncate decimal places for example the
> > report returns 36.2795358649783 when all I want is 36.
> >
> > If any one has any ideas that would be greatly appreciated.
> >
> > --
> > kmatth007

Avg Date Diff?

Has anyone had any luck getting an Avg on Date Diff? Here's what I'm
trying to do:
Figure out the average time it takes from ringtime (datetime) to
starttime(datetime).
Any ideas?Does this work for you?
SELECT SUM(DATEDIFF(SECOND, RingTime, StartTime))/COUNT(*) FROM myTable
"naomimsm@.gmail.com" wrote:
> Has anyone had any luck getting an Avg on Date Diff? Here's what I'm
> trying to do:
> Figure out the average time it takes from ringtime (datetime) to
> starttime(datetime).
> Any ideas?
>|||Sorry, I should have been clearer: I'm trying to define a formula in
Report Builder and I can't get it to give me an average--very
frustrating! Thanks!
Matt wrote:
> Does this work for you?
> SELECT SUM(DATEDIFF(SECOND, RingTime, StartTime))/COUNT(*) FROM myTable
> "naomimsm@.gmail.com" wrote:
> > Has anyone had any luck getting an Avg on Date Diff? Here's what I'm
> > trying to do:
> >
> > Figure out the average time it takes from ringtime (datetime) to
> > starttime(datetime).
> >
> > Any ideas?
> >
> >

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 values by month

Given a series of dates with values I want to find the average by averaging
the data by month and then taking the average of these values. I can do this
(see below) by creating an additional temp table but I'd like to do this
with a single SELECT. Thanks.
CREATE TABLE #TestTable
(
TestDate datetime,
TestValue decimal(18,4)
)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060101',
63138498.9442)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060102', 6018866.7861)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060103',
32423405.9143)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060104',
51402184.2973)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060105',
41984683.2967)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060106',
52707356.8602)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060107',
60084423.5696)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060108',
10256576.8087)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060109',
50973538.1018)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060110',
32250398.2328)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060111',
52293744.1444)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060112',
97993941.6661)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060113',
19633802.8665)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060114',
44710651.7875)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060115',
24443905.2030)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060116',
84725698.7918)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060117',
12220534.4395)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060118',
39001430.3432)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060119',
54180100.6619)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060120',
23901300.4697)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060121',
55804613.2757)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060122',
78239680.7124)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060123',
13036976.3434)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060124',
57277368.5493)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060125',
42169222.2497)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060126',
65064414.7238)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060127',
18749702.5770)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060128',
61961708.5242)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060129',
47072115.8125)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060130',
16382094.7146)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060131', 9684892.4976)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060201',
36461918.8525)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060202',
13637750.0447)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060203',
57475888.7342)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060204',
91835958.7101)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060205',
42083192.0164)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060206',
20161492.7101)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060207',
27152605.8613)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060208',
86874863.6639)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060209',
19168743.2577)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060210',
77360233.5777)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060211',
75986666.4099)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060212',
86183511.9510)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060213',
29403571.0172)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060214',
93603660.1728)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060215', 2679256.8156)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060216',
34113885.8418)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060217',
53605552.5087)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060218',
81263888.2651)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060219',
13779351.1302)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060220',
48140149.5757)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060221', 5541682.8948)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060222',
46000410.1362)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060223',
91160648.0837)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060224', 9857530.4764)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060225',
31939509.0333)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060226',
99050916.0463)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060227', 179190.5861)
INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060228',
80963446.2681)
CREATE TABLE #TestResults
(
TestValue decimal(18,4)
)
INSERT INTO #TestResults
SELECT
AVG(TestValue)
FROM #TestTable
GROUP BY Month(testDate)
SELECT AVG(TestValue) FROM #TestResults
DROP TABLE #TestTable
DROP TABLE #TestResultsTerri,
You can use a derived table for this like so:
SELECT AVG(MonthAvg)
FROM (SELECT AVG(TestValue) AS MonthAvg
FROM #TestTable
GROUP BY CONVERT(CHAR(6), TestDate, 112)) AS D;
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
Anything written in this message represents my view, my own view, and
nothing but my view (WITH SCHEMABINDING), so help me my T-SQL code.
"Terri" <terri@.cybernets.com> wrote in message
news:e3nr55$8d1$1@.reader2.nmix.net...
> Given a series of dates with values I want to find the average by
> averaging
> the data by month and then taking the average of these values. I can do
> this
> (see below) by creating an additional temp table but I'd like to do this
> with a single SELECT. Thanks.
> CREATE TABLE #TestTable
> (
> TestDate datetime,
> TestValue decimal(18,4)
> )
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060101',
> 63138498.9442)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060102',
> 6018866.7861)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060103',
> 32423405.9143)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060104',
> 51402184.2973)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060105',
> 41984683.2967)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060106',
> 52707356.8602)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060107',
> 60084423.5696)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060108',
> 10256576.8087)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060109',
> 50973538.1018)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060110',
> 32250398.2328)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060111',
> 52293744.1444)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060112',
> 97993941.6661)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060113',
> 19633802.8665)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060114',
> 44710651.7875)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060115',
> 24443905.2030)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060116',
> 84725698.7918)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060117',
> 12220534.4395)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060118',
> 39001430.3432)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060119',
> 54180100.6619)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060120',
> 23901300.4697)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060121',
> 55804613.2757)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060122',
> 78239680.7124)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060123',
> 13036976.3434)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060124',
> 57277368.5493)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060125',
> 42169222.2497)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060126',
> 65064414.7238)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060127',
> 18749702.5770)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060128',
> 61961708.5242)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060129',
> 47072115.8125)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060130',
> 16382094.7146)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060131',
> 9684892.4976)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060201',
> 36461918.8525)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060202',
> 13637750.0447)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060203',
> 57475888.7342)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060204',
> 91835958.7101)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060205',
> 42083192.0164)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060206',
> 20161492.7101)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060207',
> 27152605.8613)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060208',
> 86874863.6639)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060209',
> 19168743.2577)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060210',
> 77360233.5777)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060211',
> 75986666.4099)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060212',
> 86183511.9510)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060213',
> 29403571.0172)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060214',
> 93603660.1728)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060215',
> 2679256.8156)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060216',
> 34113885.8418)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060217',
> 53605552.5087)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060218',
> 81263888.2651)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060219',
> 13779351.1302)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060220',
> 48140149.5757)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060221',
> 5541682.8948)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060222',
> 46000410.1362)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060223',
> 91160648.0837)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060224',
> 9857530.4764)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060225',
> 31939509.0333)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060226',
> 99050916.0463)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060227',
> 179190.5861)
> INSERT INTO #TestTable (TestDate,TestValue)VALUES ('20060228',
> 80963446.2681)
> CREATE TABLE #TestResults
> (
> TestValue decimal(18,4)
> )
> INSERT INTO #TestResults
> SELECT
> AVG(TestValue)
> FROM #TestTable
> GROUP BY Month(testDate)
> SELECT AVG(TestValue) FROM #TestResults
>
> DROP TABLE #TestTable
> DROP TABLE #TestResults
>
>

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
>>I need to average the resulting averages from the query below
>>This query gets the averages for each TowerNumber.
>>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
>> AS AvgSupplyCalciumHardness
>>FROM tblTowers
>>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND
>>(SystemID
>>= @.SystemID) AND
>> (LocationID = @.LocationID)
>>GROUP BY LocationID, SystemID, TowerNumber
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
=====================================================When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
Thanks
On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.
|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>
|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.
|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
================================================== ===
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Averaging the Averages

I need to average the resulting averages from the query below
This query gets the averages for each TowerNumber.
SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemID
= @.SystemID) AND
(LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
What I really want is the average of the averages for
AVG(SupplyCalciumHardness * 1.00).
Something like this AVG(AVG(SupplyCalciumHardness * 1.00))
TowerNumber AvgSupplyCalciumHardness
1 14
2 18
3 7
4 8
--
Sum of averages = 47
Avg of Avg = 47 / 4 = 11.75
How can I do this?
ThanksOn Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>I need to average the resulting averages from the query below
>This query gets the averages for each TowerNumber.
>SELECT LocationID, SystemID, TowerNumber, COUNT(TowerNumber) AS
>CountEntriesPerTowerNumber, AVG(SupplyCalciumHardness * 1.00)
> AS AvgSupplyCalciumHardness
>FROM tblTowers
>WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate) AND (SystemI
D
>= @.SystemID) AND
> (LocationID = @.LocationID)
>GROUP BY LocationID, SystemID, TowerNumber
WITH ROLLUP
J.|||That comes up with 13.25, the correct answer is 11.75, so that doesn't work.
Any other idea?
"jxstern" <jxstern@.nowhere.xyz> wrote in message
news:kk8dl1p4cr94chpgo1vof4l8nefjusaekj@.
4ax.com...
> On Wed, 19 Oct 2005 14:41:55 -0500, "Craig" <NoSpam@.hotmail.com>
> wrote:
> WITH ROLLUP
>
> J.
>|||On Wed, 19 Oct 2005 15:14:44 -0500, "Craig" <NoSpam@.hotmail.com>
wrote:
>That comes up with 13.25, the correct answer is 11.75, so that doesn't work
.
>Any other idea?
You can always store the first set of results to a temp table and then
run an average on them before returning them as a set.
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
INTO #mytemp
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from #mytemp
Or, if ALL you want is the average-average, or you don't mind
computing everything twice, something like:
SELECT avg(AvgSupplyCalciumHardness) as avgavg
from
(
SELECT
LocationID, SystemID, TowerNumber,
COUNT(TowerNumber) AS CountEntriesPerTowerNumber,
AVG(SupplyCalciumHardness * 1.00) AS AvgSupplyCalciumHardness
FROM tblTowers
WHERE (ReadingDate BETWEEN @.BeginningDate AND @.EndingDate)
AND (SystemID = @.SystemID)
AND (LocationID = @.LocationID)
GROUP BY LocationID, SystemID, TowerNumber
) x
But for all I know, that may come out 13.25, too. Got some rounding
issues there, may need to cast to int to make them consistent.
Since the intermediate values you list are all ints, should the "real"
answer be 11, or 11.75, or 12?
Inquiring minds ...
J.|||Hello,
You may need to use decimal as data type instead of int. I test the
following code and it works fine:
create table avgt
(colID decimal,
colNo decimal)
insert into avgt values (1, 1)
insert into avgt values (1, 2)
insert into avgt values (1, 3)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (1, 1)
insert into avgt values (2, 1)
insert into avgt values (2, 2)
insert into avgt values (2, 3)
select avg(avg1) from
(
select colID, avg(colNo) as avg1 from avgt
group by colID) as x
I hope the information is helpful.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Averages with multi-select

I am trying to write an MDX calculation that calculates the average policy premium across multiple policies. It works fine when selecting one carrier. However, the avg function is basically based on the count and sum functions (at least that is what I read). When you select one carrier the average is based on the following:

Policy A: 2000

Policy B: 1000

Policy C: 3000

Average: 2000

Lets say I multi-select another carrier and the second carrier has the exact same amounts for the policies

the total policy amounts would be :

Policy A: 4000

Policy B: 2000

Policy C: 6000

Average: 2000

The summation (12000) should be divided by the number of policies * the number of carriers (6).

However, I wind up getting 4000 as my average. Any thoughts?

Here is the MDX I am using:

Total Policy Premiums:

sum(existing [POLICY].[POLICY #],([POLICY].[VEHICLE #].&[1],[measures].[TOTAL POLICY PREM]))

Avg:

avg(existing [POLICY].[Policy].children,[Measures].[Total Policy Premiums])

Any insight would be greatly appreciated.

You could do this for example:

AVG ( Existing CrossJoin( [Carrier].[Carrier].members, [Policy].[Policy #].members ),

[Measures].[TOTAL POLICY PREM] )

Average can be computed in multiple ways, so you need to think through on what exactly you are computing. In the MDX above, combinations of carrier and policy that do not exist or are null will not be included in the computation.

averageofChildren vs avg()/count()

hi,

which is better to use: averageofchildren or avg()/count() calculated measure, if i don't want to calculate average over time.

i have read in the "Analysis Services Performance Guide" that is better to avoid using semi-additive functions to improve performance.

Special aggregate functions require unique performance-tuning techniques. They

include DistinctCount and a collection of semiadditive aggregate functions. The

semiadditive functions include: FirstChild, LastChild, FirstNonEmpty,

LastNonEmpty, ByAccount, and AverageOfChildren.

thanks

Christina

Probably the normal average (sum/count) is the better choice if you won't consider time. AverageOfChildren requires a time dimension and Enterprise Edition.|||

i have both time dimension and enterprise edition.

but my question is, i have lots of measures in which i will be using the average. i would like to know if i use averageofchildren is convenient or better use the other way, in terms of performance.

thanks

|||

Now I am not answering your question again, but.. since the average of sum/count will be a calculated member, it will take zero processing time for the calculation, but the sum and count will take processing time (both probably much faster than AverageOfChildren). However, it will take time to calculate the value when a user executes the query. AvaregeOfChildren is used for measures and therefore will take some time to process, but then zero extra time for queries (if aggregated).

How they compare (process sum+count + calculate average vs just process) I don't know, but you can probably test this on your specific system. In general using measures is better than calculated members (faster for end users, but as said, that depends on how long it takes to process the measure vs calculate the member).

Cheers

|||

Thanks HappyCow.

i think i got the point. I'll go for averageofChildren and test accordingly. if i find it slow, will try the other way.

Hope you remain always happy..but not a cow.

Cheers

AverageOfChildren Standard Vs Enterprise

Hello,

My question may be a simple one, is there a way to do an aggregate average function without buying enterprise edition. When I try to average a dimension instead of summing (for example a field with Percents) I get a error that the AverageOfChildren feature is not avaible in the Standard version of SQL 2005. Is this really true or did I set something up wrong.

I would have a hard time convincing my company to buy Enterprise edition because basic functionality like averaging is not available in the standard edition... I have found the features by edition page: http://msdn2.microsoft.com/en-us/library/ms143761.aspx however there is nothing on here that tells you simple dimension aggregations are unavailable in the Standard Edition.

Can some one tell me if there is a way to get around this without spending 10G on an upgraded version?

Thanks,
josh1234

It is correct that some aggregation functions are not available in the Standard Edition - specifically the semi-additive ones. For an average, however, you should be able to get around the issue quite easily. Create a measure with aggregation function "Sum" for the measure you want to average. Create another measure (for the same field in the database) with the aggregation function "Count". Now, create a calculated member in the cube, which divides the sum by the count. Remember to check for division by zero:

Example:

CREATE MEMBER CURRENTCUBE.MyAverage AS

IIF([Measures].[MyCount] = 0,

NULL,

[Measures].[MySum]/[Measures].[MyCount],

NON_EMPTY_BEHAVIOR = [Measures].[MyCount];

Average Value Script

The table listed below is sampled every minute with a [CounterDateTime]
[char] (24) format.
I would like to create a script that will average the MarketValue,
FirstMarketValueA, and MarketCount fields in hourly format using the same
counterIDs.
Please help me create a script for average values.
Thanks,
CREATE TABLE [dbo].[Market_Jun2005] (
[MarketID] [int] NOT NULL ,
[RecordIndex] [int] NOT NULL ,
[CounterDateTime] [char] (24) NOT NULL ,
[MarketValue] [float] NOT NULL ,
[FirstMarketValueA] [int] NULL ,
[MarketCount] [int] NULL
)On Tue, 9 Aug 2005 14:35:44 -0700, Joe K. <Joe
K.@.discussions.microsoft.com> wrote:

>The table listed below is sampled every minute with a [CounterDateTime]
>[char] (24) format.
>I would like to create a script that will average the MarketValue,
>FirstMarketValueA, and MarketCount fields in hourly format using the same
>counterIDs.
>Please help me create a script for average values.
Hi Joe,
First, change your CounterDateTime column to datatype datetime.
After that, you can use this query:
SELECT MarketID,
DATEADD(hour,
DATEDIFF(hour, '20040101', CounterDateTime),
'20040101') AS Hour,
AVG (MarketValue) AS AvgMarketValue,
AVG (FirstMarketValueA) AS AvgFirstMarketValueA,
AVG (MarketCount) AS AvgMarketCount
FROM Market)Jun2005
GROUP BY MarketIT,
DATEADD(hour,
DATEDIFF(hour, '20040101', CounterDateTime),
'20040101')
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Average Turnaround Time - Datediff() Help

Hi everyone, I need some help with creating a report that calculates the average turnaround time in days that it takes for units to return from trips destined to a location.

The database that I am working with lists a trip each time a unit is dispatched to a destination, and then another trip is created for the units return. In the example below I am trying to calculate the number of days that it takes for a unit to return to Vancouver by calculating the difference between the departure date from Vancouver and the arrival date back into Vancouver. I then need to calculate the average number of days that it takes for a unit to return from a trip. See sample data below.

UNIT -- TRIP -- START LOCATION --START DATE--FIN LOCATION--FIN DATE
================================================== =======
U12 --001 -- VANCOUVER ---FEB 10 -- ONTARIO -- FEB 15
U10 --002 -- VANCOUVER ---FEB 13 -- ONTARIO -- FEB 18
U12 --003 -- ONTARIO ----MARCH 13 -- VANCOUVER -- MARCH 18
U10 --004 -- ONTARIO ----MARCH 1 -- VANCOUVER --MARCH 6

Unit U12 took 36 days to return back to Vancouver
Unit U10 took 21 days to return back to Vancouver

Therefore based on the two trips it takes an average of aproximately 28.5 days for a unit to return from trips destined to Ontario.try this, not a greate SQL though :)

select Route, avg(DaysTaken) as AvgDaysTaken
from (
select A.Unit,(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+ '-'+(select top 1 Fin_Location from MyTable where MyTable.Unit=A.Unit order by Trip)+'-'+(select top 1 Start_Location from MyTable where MyTable.Unit=A.Unit order by Trip) as Route, cast(A.DaysTaken as decimal(9,2)) as DaysTaken
from (
select Unit,datediff(d,min(Start_date),max(fin_date)) DaysTaken from MyTable group by unit
) A
) B
group by Route

Average Times

I have a report that displays the average time between events. I use the
following expression to calculate the time
=IIF(RowNumber("DailyInspectCount") > 1,-1 *
(DateDiff(DateInterval.Minute,Fields!InspectedByDate.Value,Previous(Fields!InspectedByDate.Value))),0)
& " min."
This gives me the number of minutes between each event just fine. Now I
want to get an overall average for the whole row. When I use the AVG
function I get the error "Aggregate functions cannot be nested inside other
aggregate functions". Does anyone know a way to get the values from this
expression to generate the average? Thanks.
JohnCorrection,
I want to get the average for the whole column not the row.
"John Wright" <riley_wright@.hotmail.com> wrote in message
news:uVA4BwvpIHA.4716@.TK2MSFTNGP06.phx.gbl...
>I have a report that displays the average time between events. I use the
>following expression to calculate the time
> =IIF(RowNumber("DailyInspectCount") > 1,-1 *
> (DateDiff(DateInterval.Minute,Fields!InspectedByDate.Value,Previous(Fields!InspectedByDate.Value))),0)
> & " min."
>
> This gives me the number of minutes between each event just fine. Now I
> want to get an overall average for the whole row. When I use the AVG
> function I get the error "Aggregate functions cannot be nested inside
> other aggregate functions". Does anyone know a way to get the values from
> this expression to generate the average? Thanks.
>
> John
>

average time of xcusion of a request sql

hi,
i m asking about : average time of xcusion of a request sql .
thks for help
The time of execution of a SQL request is really relative:
a query is well written and the design of the DB is good when the engine
satisfies the request with the minimum number of page reads. To examine the
behavior of the engine you should execute it under Query Analizer (SQL 2000)
or SSMS (SQL 2005) activating the I/O statistics:
set statistics io on
each query executed after this command will show useful information about
I/O counters.
Looking the statistics and the query plan you can undertand if your query is
working well or not
Gilberto Zampatti
"jomu" wrote:

> hi,
> i m asking about : average time of éxécusion of a request sql .
> thks for help
>