Ubuntu
openoffice.org package

Averaging fields in a Base report over a subset of values

Asked by Joe

I have a database "Blood Sugars" created in Openoffice.org Base 3.2.0. I am NOT using Sun Report Builder. This database contains blood sugar readings on a daily basis. I have created a report to print the daily readings just for a particular month. I have added a numeric field to the report that contains the Average of one of the readings. When the report displays, the value displayed is that for the average over all the entire database, not just the average for the particular month. For example, for my report for the current month of July, 2010, I have readings from just five days. I wish to display the readings for all five days in the report, followed by the average of JUST those five days. Is this possible? The value I am getting for the average is that over all the days in the database, not just the five days in July.

Question information

Language:
English Edit question
Status:
Solved
For:
Ubuntu openoffice.org Edit question
Assignee:
No assignee Edit question
Solved by:
Tony Pursell
Solved:
Last query:
Last reply:
Revision history for this message
Tony Pursell (ajpursell) said : 		#1

Hi Joe

This is a tricky one. I hope to give you a reply in a few hours.

Tony

Revision history for this message
Best Tony Pursell (ajpursell) said : 		#2

Hi Joe

What you want is a Query something like

SELECT AVG( "amount" ) as "Average" FROM "Data" WHERE MONTH("date") + YEAR("date") = MONTH(CURRENT_DATE) + YEAR(CURRENT_DATE)

Where table 'data' has columns 'amount ' and 'date'.

This averages the amounts in the current month.

Note, I have not yet tested it with a data set that contain amounts for more than one year.

You might like to see the HSQLDB documentation at

http://hsqldb.org/web/hsqlDocsFrame.html

and particularly the SQL index in the manual.

Tony

PS If this answers your question, please mark it as Solved.

Revision history for this message
Joe (jgsylvesterjr) said : 		#3

Thanks Tony Pursell, that solved my question.

Revision history for this message
Joe (jgsylvesterjr) said : 		#4

Tony:
Thanks for the time and effort you have spent looking into this problem. And, I have verified this solution does indeed works, at least on a small sample (five days in July). I am confident that it will work for my monthly reports. I will mark this as solving my problem. Thanks, again, I really appreciate it.
Joe

Revision history for this message
Tony Pursell (ajpursell) said : 		#5

Hi Joe

My solution will not work over years because the MONTH and YEAR function give numerical results. So July 2010 and June 2011 will both be 2017.

This query converts to character strings.

SELECT AVG( "amount" ) AS "Average" FROM "Data" WHERE TO_CHAR( "date", 'YYYYMM' ) = TO_CHAR( CURRENT_DATE, 'YYYYMM' )

Enjoy!

Tony

Revision history for this message
Joe (jgsylvesterjr) said : 		#6

Tony:
Thanks for the updated version of the query. I have changed my report to this and it works bueatifully. Thanks again for helping me out.
Joe