CODEWITHSUNDEEP
mysql
SasinduRHN
SasinduRHN

Reputation: 31

Is there any function for reprent final day of year in mysql

i want to add all sum of values of reg_data3 table of column rainfall to average_mas_curve table column year of last year and all of values in past data of previous years as a summation but i want to represent as a function of last day of year please give me it.......help me. e.g y-12-31. 

INSERT INTO mas_aver_curve (year, cumulative) 
    SELECT '2013-12-31', ROUND(SUM(rainfall), 1) rainfall 
    FROM reg_data3 G 
    WHERE date >= '2008-01-01' AND date <  '2013-12-31'

Upvotes: 1

Views: 284

Answers (2)

Filipe Silva
Filipe Silva

Reputation: 21657

You could try:

INSERT INTO mas_aver_curve (year, cumulative) 
    SELECT STR_TO_DATE(CONCAT(year(NOW())-1,'-12-31'),'%Y-%m-%d') AS LastDay, 
           ROUND(SUM(rainfall), 1) rainfall 
    FROM reg_data3 G 
    WHERE date >= '2008-01-01' AND date <  '2013-12-31'
    GROUP BY 1;

sqlfiddle demo for the str_to_date part

Upvotes: 1

luckylwk
luckylwk

Reputation: 225

Maybe you can select the year and sum using 

SELECT 
    YEAR(date), 
    ROUND(SUM(rainfall), 1) 
FROM reg_data3
WHERE YEAR(date)>=2008 AND YEAR(date)<=2013
GROUP BY YEAR(date)

and then put that in the insert.

Upvotes: 0

Related Questions