I have query like this :
SELECT EXTRACT(MONTH FROM d.mydate) AS synmonth, SUM(apcp) AS apcptot
FROM t_synop_data2 d
WHERE d.mydate
BETWEEN '2011-01-01' AND '2011-12-31'
AND d.idx_synop = '06712'
GROUP BY synmonth
This query adds all rain (apcp) in a month like this :
1 32.8 => from 2011.01.01 to 2011.01.31
2 27.2 => from 2011.02.01 to 2011.02.28
3 21.0
4 21.8
5 88.5
6 131.4
7 118.6
8 57.1
9 80.9
10 84.6
11 1.1
12 143.5 => from 2011.12.01 to 2011.12.31
That's what I want, but with a little difference.
This difference is that i have to adds apcp from day 2 in the month to day 1 next month and then return a result like above.
1 132.8 => from 2011.01.02 to 2011.02.01
2 27.2 => from 2011.02.02 to 2011.03.01
3 21.0
4 21.8
5 88.5
6 131.4
7 118.6
8 57.1
9 80.9
10 84.6
11 1.1
12 143.5 => from 2011.12.02 to 2012.01.01
I tried something with add_date(), extract() or date_format() but without result.
Thank you for your answer Vince
Here is the query :
SELECT EXTRACT(MONTH FROM ADDDATE(d.mydate,-1) ) AS synmonth
, SUM(apcp) AS apcptot
FROM t_synop_data2 AS d
WHERE ADDDATE(d.mydate,-1) BETWEEN '2011-01-01' AND '2012-12-31'
AND d.idx_synop = '06712'
GROUP BY synmonth
You can check the result by adding two columns like this:
SELECT EXTRACT(MONTH FROM ADDDATE(d.mydate,-1) ) AS synmonth
, SUM(apcp) AS apcptot
, MIN(d.mydate) AS date_min
, MAX(d.mydate) AS date_max
FROM t_synop_data2 AS d
WHERE ADDDATE(d.mydate,-1) BETWEEN '2011-01-01' AND '2012-12-31'
AND d.idx_synop = '06712'
GROUP BY synmonth
You can group by EXTRACT(MONTH FROM d.mydate - INTERVAL 1 DAY)
SELECT EXTRACT(MONTH FROM d.mydate) AS synmonth, SUM(apcp) AS apcptot
FROM t_synop_data2 d
WHERE d.mydate
BETWEEN '2011-01-01' AND '2011-12-31'
AND d.idx_synop = '06712'
GROUP BY EXTRACT(MONTH FROM d.mydate - INTERVAL 1 DAY)