I’m fairly new to LO Base and I’ve looked in a table, query and form to see if there was a sum function available.
I want to get the average of one set of figures, and the total of another set.
How can this be done in base?
SQL
Example:
SELECT DATE, SUM(AMOUNT), AVG(AMOUNT) FROM MY_TABLE GROUP BY DATE
Edit:
OK found your problem. It dates way back to the original problem. ALL the fields were TEXT fields. You cannot SUM or AVG on a TEXT field!
To get around this, first (luckily having an old sample) I copied the table for backup. Then I edited the table and changed TempMaxC and RainFall to type Double.
Then used this for the query:
SELECT AVG("TempMaxC"), SUM("RainFall") FROM "tblWXdataLeeds" WHERE "ReadingDate" BETWEEN '2017-04-01' AND '2017-04-30' Group by YEAR("ReadingDate")
which results in:
Again, you really need to learn SOME SQL. This is the easier stuff.
Just to put you on your toes, with a little SQL you can produce this:
This was done with the data/DB I have and this SQL:
SELECT SELMONTH AS "Month", SELYEAR AS "Year", AVG( A."TempMaxC" ) "AVG Temp", SUM( A."RainFall" ) "Total Rain" FROM (SELECT YEAR( "ReadingDate" ) SELYEAR, MONTH( "ReadingDate" ) AS SELMONTH, "TempMaxC", "RainFall" FROM "TestTable2" WHERE YEAR("ReadingDate") > '2015')A GROUP BY A.SELMONTH, A.SELYEAR
If this answers your question please click on the 
(upper left area of answer).
Apologies, I should have said that there are different figures on different dates, and I want to sum the figures for each date. i.e
02/04/2017 Max temp 17.4, Rainfall 2mm, then 03/04/2017 Max temp 17.9, Rainfall 5.6.
From a list of dates in a month I want to average the temperature, and total the rainfall.
I hope that makes it more clearer for you.
That makes a big difference from your original question but it still is a matter of SQL and grouping.
How would I group it by if not DATE
I’ve just run this in the Tools/SQL window:
SELECT SUM(RainFall), AVG(TempMaxC) FROM “tblWXdataLeeds” WHERE “ReadDate” BETWEEN {d ‘2017-05-01’ } AND {d ‘2017-05-31’ }
and it returned this:Set Function on non-numeric data is not allowed
You have no GROUP BY in your statement which necessary for SUM & AVG.
Where does it fit in then, as I can’t grasp how.
Thank you again, and I will find the second query very useful.
PS, I’ve just ordered SQL for Dummies.