Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

SQL

Example:

SELECT DATE, SUM(AMOUNT), AVG(AMOUNT) FROM  (SELECT DATE, AMOUNT FROM MY_TABLE) A GROUP BY DATE

SQL

Example:

SELECT DATE, SUM(AMOUNT), AVG(AMOUNT) FROM  (SELECT DATE, AMOUNT FROM MY_TABLE) A GROUP BY DATE

SQL

Example:

SELECT DATE, SUM(AMOUNT), AVG(AMOUNT) FROM MY_TABLE GROUP BY DATE

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.

If this answers your question please click on the ✔ (upper left area of answer).

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