# Can sum of figures in a column be done?

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?

edit retag close merge delete

Sort by » oldest newest most voted

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


more

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.

( 2017-05-31 21:28:22 +0100 )edit

That makes a big difference from your original question but it still is a matter of SQL and grouping.

( 2017-05-31 21:32:00 +0100 )edit

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

( 2017-05-31 21:35:44 +0100 )edit

You have no GROUP BY in your statement which necessary for SUM & AVG.

( 2017-05-31 21:46:47 +0100 )edit

Where does it fit in then, as I can't grasp how.

( 2017-05-31 21:57:42 +0100 )edit

Thank you again, and I will find the second query very useful. PS, I've just ordered SQL for Dummies.

( 2017-06-01 09:29:24 +0100 )edit