1

I have a database that has this

Date       -   Temp

2013-12-01 -   12    
2013-12-31 -   18

Holds 1 month. Im graphing this by JSON in my PHP page. But I want something different, to graph it weekly. Like It will take each 7 days temp average and if I find a suitable sql statement for it, it would be great.

I found this in my pdf. 

SELECT Date,Temp COUNT(Date) AS weekly, AVG(Temp) AS Temperature 
    FROM temp2 GROUP BY Date ORDER BY Date;

But it will take all the 31 days which is not weekly but monthly.

Anyways, is there something you can do?

Thanks.

jogesh_pi
  • 9,762
  • 4
  • 37
  • 65
Atik
  • 89
  • 1
  • 1
  • 12
  • 2
    `SELECT YEARWEEK(date)` and `GROUP BY YEARWEEK(Date)`: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_yearweek – Mark Baker Dec 28 '13 at 11:46
  • possible duplicate of [Group by month and year in MySQL](http://stackoverflow.com/questions/3366895/group-by-month-and-year-in-mysql) – Calimero Dec 28 '13 at 11:51
  • I want average of temps 7 days by 7 days, so in the end I will have a table like 4 rows. – Atik Dec 28 '13 at 12:05
  • http://stackoverflow.com/questions/6997814/mysql-average-every-4-rows OK Found the answer here! – Atik Dec 28 '13 at 12:11
  • So in what way does YEARWEEK() fail? – Mark Baker Dec 28 '13 at 12:12
  • I have what I wanted right now but I also want to have another column named as Week and values are the row counts which is 4 or 5 atmost. I used something but it give 7 7 7 7 3 which is day count. How can I count the generated rows and print them like 1,2,3,4,5? – Atik Dec 28 '13 at 12:26
  • OK Done that aswell. Thanks for replies btw. – Atik Dec 28 '13 at 13:07

0 Answers0