Search code examples
sqliteaveragetemperature

Average temperature by day of year with SQLite DDB

I'm looking for a good solution to show the average temperature day by day of the year from a SQLite Database.

In my database, to be sample, I have a date column and a temp column, for each day since 5 years like that.

example

I would like to get, for each day of the year, the average temperature from my database. I found the request to calculate the average for one day, but I don't how can I do like that for each day

SELECT avg(min) FROM historique WHERE strftime('%m-%d', date )= "04-01";

Could you help me please ?

Solution

  • You must use use GROUP BY:

    SELECT strftime('%m-%d', date) day, avg(min) 
FROM historique 
GROUP BY day