Re: [libreoffice-users] Re: Base Reports - Getting Monthly & YTD Averages

Sure.

The table:
  id date amount_mm  0 08/12/14 22  1 08/19/14 8  2 08/24/14 9.5  3 09/04/14
52  4 09/11/14 20  6 09/12/14 20  7 09/13/14 34  8 09/17/14 42  9 09/24/14 7
The underlying query:
SELECT MONTHNAME( "date" ) AS "Month", SUM( "amount_mm" ) AS "Total (mm)",
AVG( "amount_mm" ) AS "Avg (mm)", MAX( "amount_mm" ) AS "Max (mm)", MIN(
"amount_mm" ) AS "Min (mm)", SUM( "amount_mm" * 0.0393701 ) AS "Sum (in)",
AVG( "amount_mm" * 0.0393701 ) AS "Avg (in)", MAX( "amount_mm" * 0.0393701
) AS "Max (in)", MIN( "amount_mm" * 0.0393701 ) AS "Min (in)" FROM
"tblRain" GROUP BY MONTHNAME( "date" )

On Sat, Sep 27, 2014 at 5:49 AM, Alex Thurgood <alex.thurgood@gmail.com>
wrote:

> Le 26/09/2014 11:03, Don Parris a écrit :
> > Thanks Alex.  The underlying query gets the month from the date, as well
> as
> > the total, average, max and min for each month.  The results are, of
> > course, already grouped & calculated by month.  I was able to get the
> > overall total, but also wanted to see the overall average, max and min.
> I
> > somehow imagined that it would be a piece of cake.  It looks like Calc is
> > the better "report tool" in this case.
>
> Having never really played around with Calc and database datasets, I
> admit to not knowing, but I imagine that the function set is broader and
> thus allows for more possibilities.
>
> Would you mind posting your initial query and table definition so that I
> could build my own little test db and have a think about it, otherwise
> its all a bit too abstract ?
>
> Alex
>
>
> --
> To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
> Problems?
> http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
> Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
> List archive: http://listarchives.libreoffice.org/global/users/
> All messages sent to this list will be publicly archived and cannot be
> deleted



-- 
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris>
<http://www.linkedin.com/in/dcparris>
GPG Key ID: F5E179BE

-- 
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted