CODEWITHSUNDEEP
mysqlsqldatabase
Madoc Comadrin
Madoc Comadrin

Reputation: 568

Mysql: Execute query for each date

I am using Mysql 5.5.43-0+deb7u1.

I have managed to create this query to fetch rain data for particular day (I use MODbecause rain data is updated at every full hour while other data gets update more frequently.)

SELECT date,SUM(rainmm) FROM weather WHERE MOD(minute(time),60)=0 AND date=CURDATE()-1;
+------------+-------------+
| date       | sum(rainmm) |
+------------+-------------+
| 2015-06-23 |     0.1     |
+------------+-------------+
1 row in set (0.15 sec)

I am looking for a way to get results for all days with single query instead of needing to check every day manually. I am unsure if I should use loops and iterate number for days from current date or if there is better way to accomplish this.

Desired result:

SELECT date, [Smart query to get all days at once]
+------------+-------------+
| date       | sum(rainmm) |
+------------+-------------+
| 2015-06-23 |     0.1     |
| 2015-06-24 |     0.0     |
|    ...     |     ...     |
| 2015-11-11 |     11.1    |
+------------+-------------+

Upvotes: 0

Views: 1032

Answers (1)

PaulF
PaulF

Reputation: 6773

Does this do what you want

SELECT date,SUM(rainmm) FROM weather WHERE MOD(minute(time),60)=0
GROUP BY date
ORDER BY date;

Upvotes: 3

Related Questions