Writing a query to get a sum of a group average

[Guest]

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

 

[Jason Lepack]

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

 

[Guest]

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]

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

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.....

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 -

 

[Guest]

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)

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

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.....

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 -

 

[Jason Lepack]

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)

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

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.....
:
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.

 

[Guest]

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

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)

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

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.....

:
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.

 

[Guest]

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?

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

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)



:
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

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.....

:
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.

 

[Jason Lepack]

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

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

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)
:
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
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.....
:
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 -