Writing a query to get a sum of a group average

Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn

Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....


"Jason Lepack" wrote:

On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn

SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate

On Feb 6, 2:35 pm, ktm400 wrote:
Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....



"Jason Lepack" wrote:
On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn- Hide quoted text -

- Show quoted text -

Thanks Jason.
When I run the query I get this message:

You tried to execute a query that does not include the specified expression
"hour(readingtime)" as part of an aggregate function. (Error 3122)


"Jason Lepack" wrote:

SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate

On Feb 6, 2:35 pm, ktm400 wrote:
Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....



"Jason Lepack" wrote:
On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn- Hide quoted text -

- Show quoted text -

On Feb 7, 12:30 pm, ktm400 wrote:
Thanks Jason.
When I run the query I get this message:

You tried to execute a query that does not include the specified expression
"hour(readingtime)" as part of an aggregate function. (Error 3122)



"Jason Lepack" wrote:
SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate

On Feb 6, 2:35 pm, ktm400 wrote:
Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....

"Jason Lepack" wrote:
On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Copy your SQL from the Query and paste it here.

SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate


"Jason Lepack" wrote:

On Feb 7, 12:30 pm, ktm400 wrote:
Thanks Jason.
When I run the query I get this message:

You tried to execute a query that does not include the specified expression
"hour(readingtime)" as part of an aggregate function. (Error 3122)



"Jason Lepack" wrote:
SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate

On Feb 6, 2:35 pm, ktm400 wrote:
Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....

"Jason Lepack" wrote:
On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Copy your SQL from the Query and paste it here.

Well - this works, but Iam not sure why.

SELECT sum(AvgOfCurrentSteamFlow)
FROM [SELECT dbo_Boiler_Steam_Production.ReadingDate AS Expr1,
Hour([ReadingTime]) AS Expr3,
Avg(dbo_Boiler_Steam_Production.CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM dbo_Boiler_Steam_Production
GROUP BY dbo_Boiler_Steam_Production.ReadingDate, Hour([ReadingTime])
HAVING (((Year([ReadingDate]))=[Select Year]))]. AS [%$##@_Alias];

What is the "AS [%$##@_Alias]" part?

"ktm400" wrote:

SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate


"Jason Lepack" wrote:

On Feb 7, 12:30 pm, ktm400 wrote:
Thanks Jason.
When I run the query I get this message:

You tried to execute a query that does not include the specified expression
"hour(readingtime)" as part of an aggregate function. (Error 3122)



"Jason Lepack" wrote:
SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate

On Feb 6, 2:35 pm, ktm400 wrote:
Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....

"Jason Lepack" wrote:
On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Copy your SQL from the Query and paste it here.

I had the alias for the Hour(ReadingTime) in the group by instead of
Hour(ReadingTime).

SELECT ReadingDate, Sum(AvgFlow) AS SumOfAvgFlow
FROM (SELECT ReadingDate, Hour(readingtime) AS ReadingHour,
Avg(CurrentSteamFlow) AS AvgFlow
FROM [Steam Production]
GROUP BY ReadingDate, Hour(readingtime)
HAVING Year([ReadingDate])=[Year]) AS A
GROUP BY ReadingDate;

Cheers,
Jason Lepack

On Feb 7, 2:09 pm, ktm400 wrote:
SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate



"Jason Lepack" wrote:
On Feb 7, 12:30 pm, ktm400 wrote:
Thanks Jason.
When I run the query I get this message:

You tried to execute a query that does not include the specified expression
"hour(readingtime)" as part of an aggregate function. (Error 3122)

"Jason Lepack" wrote:
SELECT ReadingDate, sum(AvgOfCurrentSteamFlow)
FROM
(SELECT ReadingDate,
hour(readingtime) AS ReadingHour,
avg(CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY ReadingDate, ReadingHour
HAVING year(ReadingDate) = [Year])
GROUP BY ReadingDate

On Feb 6, 2:35 pm, ktm400 wrote:
Thank you.
I ended up with this expression:

SELECT [Steam Production].ReadingDate, Hour([readingtime]) AS ReadingHour,
Avg([Steam Production].CurrentSteamFlow) AS AvgOfCurrentSteamFlow
FROM [Steam Production]
GROUP BY [Steam Production].ReadingDate, Hour([readingtime])
HAVING (((Year([ReadingDate]))=[Year]));

What I need to do now is sum the AvgOfCurrentSteamFlow column.
I can't seem to figure it out without creating a seperate query.....

"Jason Lepack" wrote:
On Feb 6, 12:50 pm, ktm400 wrote:
Please help -
I have a table with 3 columns. One of the columns is date, one is time and
the last one is the value I want to average.
What I need to do is group the third column (my value) by hour and date
(there are 4 entries/hour) then average the 4 readings for the hour and then
add those averages together.

I'm quite new to queries so not sure how to write the query.

Thanks for any help

This will calculate your daily total of hourly averages:

SELECT dateColumn, sum(hourlyAverage)
FROM
(SELECT dateColumn,
hour(timeColumn) AS hourColumn,
avg(averageColumn) AS hourlyAverage
FROM yourTable
GROUP BY dateColumn, hourColumn)
GROUP BY dateColumn- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

Copy your SQL from the Query and paste it here.- Hide quoted text -

- Show quoted text -