Triggers that inserts values to a table from another table
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