Create trigger to insert rows from one table to another with sum operation (group by) I have two tables in my database traffic_shift and traffic_daily. With following columns.

Table traffic_shift :-

day     month    year    toll_id    shift    car_single    car_return 
 1       9        2016    1          1        10            5
 1       9        2016    1          2        100           50
 1       9        2016    1          3        200           100

 1       9        2016    2          1        15            20
 1       9        2016    2          2        30            40
 1       9        2016    2          3        45            60

 2       9        2016    1          1        12            6
 2       9        2016    1          2        24            12
 2       9        2016    1          3        36            18

 2       9        2016    2          1        18            9
 2       9        2016    2          2        36            18
 2       9        2016    2          3        54            27


traffic_daily table will be like this,

Table traffic_daily

day     month    year    toll_id    car_single    car_return 
 1       9       2016     1          310          155
 1       9       2016     2          90           120
 2       9       2016     1          60           30
 2       9       2016     2          108          54


As you can see there are 3 shift in a day for any toll. So the functioning of the trigger should be :

it should sum up the car_single and car_return, date wise for each toll and should insert into the table traffic_daily as shown in above table.

Please help me out with this. I am just stuck at this problem.

Thanks in advance.

EDIT

This is what i could come up with. 

DROP TRIGGER after_insert_shift;
DELIMITER $$

CREATE TRIGGER `test`.`after_insert_shift` 
AFTER INSERT ON `test`.`shift`
FOR EACH ROW 
BEGIN
INSERT INTO daily   
SET
`day` = new.day,
`month` = new.month,
`year`= new.year,
toll_id = new.toll_id,
car_single = SELECT SUM(car_single) 
    WHERE daily.day = new.day , daily.month = new.month, daily.year = new.month ,
    daily.toll_id = new.toll_id FROM shift GROUP BY 
    shift.day,shift.month,shift.year,shift.toll_id;

car_return = SELECT SUM(car_return) WHERE daily.day = new.day , 
    daily.month = new.month, daily.year = new.month , 
    daily.toll_id = new.toll_id FROM shift GROUP BY
    shift.day,shift.month,shift.year,shift.toll_id;


END$$

DELIMITER ;
First observation, FROM is usually before WHERE, i.e. 

car_single = SELECT SUM(car_single) FROM shift WHERE ... 


Second observation, INSERTnormally have the form: 

INSERT INTO daily (day, month, ... ) 
VALUES (...)


These are the syntactical errors I find in your trigger. Logically I think there is more to do. Your trigger will insert a new row into daily every time a new row is inserted into shift, from your description that does not sound right. Here's what I would try to do (only a sketch).

CREATE TRIGGER `test`.`after_insert_shift` 
AFTER INSERT ON `test`.`shift`
FOR EACH ROW 
BEGIN
    UPDATE daily SET (car_single, car_return) = (
        SELECT SUM(car_single), SUM(car_return)
        FROM shift
        WHERE day = new.day 
          AND daily.month = new.month
          AND daily.year = new.year 
          AND ...
    )
    WHERE daily.month = new.month
      AND daily.year = new.year
      AND ...  

    -- Check if number of rows updated = 0, if so insert a new row
    SELECT ROW_COUNT() INTO N;
    IF N = 0 THEN
        INSERT INTO daily (c1, c2, c3, ...)
        SELECT ...


Don't know if that's your problem, but I would start there.