2

I have two columns "date" and "temperature". I want to select average temperature between two specific dates e.g. 1 - 7 April grouped by year. When i use this query

select avg(temperature) from table where dayofyear(date) between 91 and 97 group by year(date)

two problems appear.

  1. In leap years dayofyear for days after 28 February has different value
  2. When I want select average from e.g. between 30 December and 5 January this query: select avg(temperature) from table where dayofyear(date) between 364 and 5 group by year(date) returns 'null'.

In coresponding to point 2 is another problem. How to receive data between e.g. 30 December (a year before) and 5 January grouped by year?

Robson
  • 55
  • 4

1 Answers1

1

For the first question, use the SQL DAY() and MONTH() functions:

SELECT AVG(temperature)
FROM table
WHERE DAY(date) between 1 and 7 AND MONTH(date) = 4
GROUP BY YEAR(date)

For the second part, you may have to split it into two parts.

KYDronePilot
  • 527
  • 3
  • 12
  • That's period was only for example. When period contains two different months your query returns incorrect values. – Robson Apr 14 '19 at 14:31
  • Would enclosing the month and day sections in parenthesis and adding multiple of them using the ‘OR’ operator work? – KYDronePilot Apr 14 '19 at 14:38
  • I take the datas from some variable in my code. So I don't know when period contains one and when two months. I can use some 'IF' operations with your solution but I looking for simpler methods. – Robson Apr 14 '19 at 18:39
  • What exact type of data are you getting from your code? Two days of year? Two days of month and months of year? – KYDronePilot Apr 14 '19 at 18:43
  • Date in format 'yyyy-mm-dd'. Modify code to extracting day and month number is no problem for me – Robson Apr 15 '19 at 04:12
  • Will this work? https://stackoverflow.com/a/3822668/11354266 Just set the time to something like 00:00:00 – KYDronePilot Apr 15 '19 at 10:23
  • With this solution grouping by year (date) will not working – Robson Apr 15 '19 at 11:05
  • You could do `GROUP BY DAY()` for grouping by day or `GROUP BY YEAR()` for grouping by year. – KYDronePilot Apr 15 '19 at 13:56
  • When you use date which contains any year then grouping by year returns one row with data with year from query... – Robson Apr 15 '19 at 16:03
  • Yeah, it may just be easier to modify the code to extract the month and year – KYDronePilot Apr 15 '19 at 17:28
  • So, what when period contains dates from two different years, for example since 29 December (a year before) to 4 January? – Robson Apr 15 '19 at 19:17
  • Good point... hmm... Back to my suggestion 2 comments ago, it would only be grouping into one year since your query is limiting it to a specific year – KYDronePilot Apr 15 '19 at 19:37
  • Yes, for this reason this is no solution for me. Thanks – Robson Apr 16 '19 at 04:18