Triggers that inserts values to a table from another table I am trying to create a report table that contains drug_id, total sales and average sales from an inventories table. This code doesn't work as it should. It duplicates records and when I insert additional rows in inventories, it doesn't reflect. I am new to triggers and complex sql queries, please assist.  

DELIMITER //
CREATE TRIGGER `report` AFTER INSERT ON `inventories`

FOR EACH ROW 
  insert into report (drug_id,total_sales,average_sales,amount_remaining) 
 select  

i.drug_id,
coalesce(p.total_sales, 0) total_sales,
coalesce(s.average_sales, 0) average_sales,
coalesce(t.amount_remaining, 0) amount_remaining

from inventories i
left join
(
 select drug_id, sum(quantity_sold) total_sales 
from inventories
WHERE MONTH(complete_sold) = 01 
group by drug_id
) p
on i.drug_id = p.drug_id
left join
(
select drug_id, avg(quantity_sold) average_sales
 from inventories
WHERE complete_sold BETWEEN '2016-11-01' AND '2016-12-31'
group by drug_id
) s
 on i.drug_id = s.drug_id
 left join
(
  select drug_id, sum(amount_remaining) amount_remaining
from inventories
 group by drug_id
 ) t
 on i.drug_id = t.drug_id

 //
DELIMITER ;
It appears you have ommited the BEGIN and END tags in your TRIGGER declaration.

COALESCE(), SUM(), and AVG() calls need a table reference, looks like your passing a presumed column reference, also FROM keyword is missing after these calls.

Its difficult to follow your database structure from this example.  Tables 'report' and 'inventories' are clear enough, but 'i', 'p', 's', 't' are not.  The column names are descriptive, but its not always clear what table or tables they reside in.

The one thing that sticks out most to me is two table names supplied in the FROM clause on line 13, and the extra table supplied after all three closing parentheses in your LEFT JOIN statements.

Are you wanting this report to be run AFTER every time an UPDATE query is performed on the 'inventories' table?  You may be better served by building a INSERT query that builds this report on demand, see if it produces what you want, and then threading it into a TRIGGER if you want it automated.

For example, something like this, presuming it's supplied a specific drug_id to run with.  Though, with a separate database call and a while loop, you could easily run this on every drug_id.

INSERT INTO report ( drug_id, total_sales, average_sales, amount_remaining) 
   VALUES (   
( SELECT drug_id FROM i WHERE drug_id = $report_focus),
( SELECT COALESCE(total_sales, 0) FROM p WHERE MONTH(complete_sold) = 01 ),
( SELECT COALESCE(average_sales, 0) FROM s WHERE complete_sold BETWEEN '2016-11-01' AND '2016-12-31'),
( SELECT COALESCE(amount_remaining, 0) FROM t)
   );


I find it helpful to use ALL CAPS on MySQL SQL commands and functions for easier seperation between keywords and content.

Helpful links:
MySQL Trigger Syntax
Hive SQL query checker