Calculating 12 monthly averages after summing daily values

[Guest]

This query sums a calculated daily value for mgd for January and sorts to
show a maximum daily value. Fields are date, time, gpm, mgd.
There are hundreds of values for each day of the month.

PARAMETERS [What month?] Byte;
SELECT [AWY003~1].Date AS Expr1, Sum([gpm]*10/1000000) AS mgd
FROM [AWY003~1]
WHERE (((Month([date]))=[What month?]))
GROUP BY [AWY003~1].Date
ORDER BY Sum([AWY003~1].gpm) DESC;

How can the same query also find the average of the 31 daily values of mgd?
How can this query then be expanded to include all twelve months?

 

[MGFoster]

This query sums a calculated daily value for mgd for January and sorts to
show a maximum daily value. Fields are date, time, gpm, mgd.
There are hundreds of values for each day of the month.

PARAMETERS [What month?] Byte;
SELECT [AWY003~1].Date AS Expr1, Sum([gpm]*10/1000000) AS mgd
FROM [AWY003~1]
WHERE (((Month([date]))=[What month?]))
GROUP BY [AWY003~1].Date
ORDER BY Sum([AWY003~1].gpm) DESC;

How can the same query also find the average of the 31 daily values of mgd?
How can this query then be expanded to include all twelve months?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean by "average of the 31 daily values of mgd." Do
you mean average of 31 values per day? I'll assume this in my solution,
below.

BTW, "Date" shouldn't be used as a column name, 'cuz Date is a VBA
function that returns the current system time. It can be confusing to
read the SQL and wonder if "Date" is a column name or the VBA function.
Anyway, "Date" is an awful name to describe some date in the DB. Is it
the work_date, sent_date, consumption_date, death_date, birth_date,
etc.?

To get an average use the Avg() aggregate function, or create your own
averaging formula. To get all months, use a trick to show all months
when the user doesn't enter a month in the prompt. E.g.:

PARAMETERS [What month?] Byte;
SELECT [Date] AS TheDate, Sum([gpm]*10/1000000) AS mgd,
Sum([gpm]*10/1000000)/Count([gpm]) As DailyAvgMGD
FROM [AWY003~1]
WHERE (Month([date])=[What month?] AND [What month?] IS NOT NULL)
OR [What month?] IS NULL
GROUP BY [Date]
ORDER BY [Date], 2 DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtgI6YechKqOuFEgEQJttgCfV/nh4jkWpUtSm7/Hm4jwVVlW0W4AoP9r
eCy43whbreCFOZuP0jYp/Jwj
=/XpR
-----END PGP SIGNATURE-----

 

[Guest]

"average of the 31 daily values of mgd." This is misleading.
The first question to answer is which day of the month has the highest mgd
based on a measurement every 10 minutes?
The second question is what is the average mgd over the entire month which
consists of 144 measurements per day times 31 days. Would it be simpler to
use 2 queries?

This query sums a calculated daily value for mgd for January and sorts to
show a maximum daily value. Fields are date, time, gpm, mgd.
There are hundreds of values for each day of the month.

PARAMETERS [What month?] Byte;
SELECT [AWY003~1].Date AS Expr1, Sum([gpm]*10/1000000) AS mgd
FROM [AWY003~1]
WHERE (((Month([date]))=[What month?]))
GROUP BY [AWY003~1].Date
ORDER BY Sum([AWY003~1].gpm) DESC;

How can the same query also find the average of the 31 daily values of mgd?
How can this query then be expanded to include all twelve months?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean by "average of the 31 daily values of mgd." Do
you mean average of 31 values per day? I'll assume this in my solution,
below.

BTW, "Date" shouldn't be used as a column name, 'cuz Date is a VBA
function that returns the current system time. It can be confusing to
read the SQL and wonder if "Date" is a column name or the VBA function.
Anyway, "Date" is an awful name to describe some date in the DB. Is it
the work_date, sent_date, consumption_date, death_date, birth_date,
etc.?

To get an average use the Avg() aggregate function, or create your own
averaging formula. To get all months, use a trick to show all months
when the user doesn't enter a month in the prompt. E.g.:

PARAMETERS [What month?] Byte;
SELECT [Date] AS TheDate, Sum([gpm]*10/1000000) AS mgd,
Sum([gpm]*10/1000000)/Count([gpm]) As DailyAvgMGD
FROM [AWY003~1]
WHERE (Month([date])=[What month?] AND [What month?] IS NOT NULL)
OR [What month?] IS NULL
GROUP BY [Date]
ORDER BY [Date], 2 DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtgI6YechKqOuFEgEQJttgCfV/nh4jkWpUtSm7/Hm4jwVVlW0W4AoP9r
eCy43whbreCFOZuP0jYp/Jwj
=/XpR
-----END PGP SIGNATURE-----

 

[Guest]

your "daily average mgd" is actually the average mgd for any 10-minute
interval during the day. Multiply that number by 144 to get the average mgd
for the whole day.

This query sums a calculated daily value for mgd for January and sorts to
show a maximum daily value. Fields are date, time, gpm, mgd.
There are hundreds of values for each day of the month.

PARAMETERS [What month?] Byte;
SELECT [AWY003~1].Date AS Expr1, Sum([gpm]*10/1000000) AS mgd
FROM [AWY003~1]
WHERE (((Month([date]))=[What month?]))
GROUP BY [AWY003~1].Date
ORDER BY Sum([AWY003~1].gpm) DESC;

How can the same query also find the average of the 31 daily values of mgd?
How can this query then be expanded to include all twelve months?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure what you mean by "average of the 31 daily values of mgd." Do
you mean average of 31 values per day? I'll assume this in my solution,
below.

BTW, "Date" shouldn't be used as a column name, 'cuz Date is a VBA
function that returns the current system time. It can be confusing to
read the SQL and wonder if "Date" is a column name or the VBA function.
Anyway, "Date" is an awful name to describe some date in the DB. Is it
the work_date, sent_date, consumption_date, death_date, birth_date,
etc.?

To get an average use the Avg() aggregate function, or create your own
averaging formula. To get all months, use a trick to show all months
when the user doesn't enter a month in the prompt. E.g.:

PARAMETERS [What month?] Byte;
SELECT [Date] AS TheDate, Sum([gpm]*10/1000000) AS mgd,
Sum([gpm]*10/1000000)/Count([gpm]) As DailyAvgMGD
FROM [AWY003~1]
WHERE (Month([date])=[What month?] AND [What month?] IS NOT NULL)
OR [What month?] IS NULL
GROUP BY [Date]
ORDER BY [Date], 2 DESC

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtgI6YechKqOuFEgEQJttgCfV/nh4jkWpUtSm7/Hm4jwVVlW0W4AoP9r
eCy43whbreCFOZuP0jYp/Jwj
=/XpR
-----END PGP SIGNATURE-----

 

[MGFoster]

"average of the 31 daily values of mgd." This is misleading.
The first question to answer is which day of the month has the highest mgd
based on a measurement every 10 minutes?
The second question is what is the average mgd over the entire month which
consists of 144 measurements per day times 31 days. Would it be simpler to
use 2 queries?

:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, another query will be necessary for the avg mgd over the month,
since the 1st query is per day

You could change my query to this:

PARAMETERS [What month?] Byte;
SELECT [Date] AS TheDate, Sum([gpm]*10/1000000) AS mgd,
(Sum([gpm]*10/1000000)/Count([gpm]))*144 As DailyAvgMGD
FROM [AWY003~1]
WHERE (Month([date])=[What month?] AND [What month?] IS NOT NULL)
OR [What month?] IS NULL
GROUP BY [Date]
ORDER BY 2 DESC

And then the Monthly avg query would be like this:

SELECT Month([Date]) As TheMonth, Avg(DailyAvgMGD) As MonthlyAvg
FROM Query1
ORDER BY 2 DESC

ORDER BY 2 means sort the 2nd column in the SELECT column-list.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtiNdYechKqOuFEgEQK9lQCglf3/+Pp+X9G1eoKW7bypOBBLNi8AoLZj
odp/xrbX2hQOCiwzNbn4w1LH
=8sF9
-----END PGP SIGNATURE-----