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