Ask Your Question
0

Can sum of figures in a column be done?

asked 2017-05-31 20:50:23 +0100

Avvy65 gravatar image

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 flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-05-31 21:15:55 +0100

Ratslinger gravatar image

updated 2017-06-01 04:50:41 +0100

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: image description

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:

image description

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

edit flag offensive delete link more

Comments

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.

Avvy65 gravatar imageAvvy65 ( 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.

Ratslinger gravatar imageRatslinger ( 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

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

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

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

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

Avvy65 gravatar imageAvvy65 ( 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.

Avvy65 gravatar imageAvvy65 ( 2017-06-01 09:29:24 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-05-31 20:50:23 +0100

Seen: 218 times

Last updated: Jun 01 '17