MySQL – Creating a Trigger to Store Stock Levels After Sale

MySQL

I have a basic database for a retail store with 4 tables, one of which stores Order info, one that stores Product info and one that stores Supplier info.

I am trying to run a query that will display total sum of stock sold figure (in dollars) with a GROUP BY supplier with ROLL UP. I have a feeling that SUM should be in the code but I can't find where to put it, so far I have this:

SELECT ORDERS.OrderPrice, PRODUCT.SupplierID, SUPPLIER.SupplierName
FROM ((ORDERS
INNER JOIN PRODUCT ON ORDERS.ProductID = PRODUCT.ProductID)
INNER JOIN SUPPLIER ON PRODUCT.SupplierID = SUPPLIER.SupplierID)
GROUP BY SupplierName WITH ROLLUP;

The database can be viewed on DB Fiddle here:

https://www.db-fiddle.com/f/qS515ShyFGxgj4h6Ga6HA3/1#&togetherjs=aNgE8G0yMh

Any ideas? Thanks

Best Answer

You must use SUM function with orderPrice

SELECT SUM(ORDERS.OrderPrice), SUPPLIER.SupplierName FROM ((ORDERS INNER JOIN PRODUCT ON ORDERS.ProductID = PRODUCT.ProductID) INNER JOIN SUPPLIER ON PRODUCT.SupplierID = SUPPLIER.SupplierID) GROUP BY SupplierName with ROLLUP

Related Solutions

Mysql – index doesn’t work properly

I am not a MySQL expert (or even a user!) but the ideal index for your query appears to be as shown below:

create table `single` (
  `game_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` mediumint(9) NOT NULL,
  `stage` tinyint(4) NOT NULL,
  `highscore` bigint(20) unsigned NOT NULL,
  `played_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`game_id`),
  KEY `user_id_stage` (`stage`,`user_id`, `highscore`)
)

The plan I get is:

Explain

See the SQLfiddle

Mysql – Creating a MySQL Trigger

Try

CREATE TRIGGER tg_ai_tablea
AFTER INSERT ON tablea
FOR EACH ROW
  INSERT INTO tableb (userid, value)
  VALUES (NEW.userid, CASE NEW.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END);

Here is SQLFiddle demo

You can also consider instead of storing value just evaluating it on the fly

SELECT b.userid, 
       CASE a.type WHEN 'admin' THEN 1 WHEN 'user' THEN 2 END value
       -- , other columns from tableb
  FROM tableb b JOIN tablea a
    ON b.userid = a.user.id;

Best Answer

You must use SUM function with orderPrice

Related Solutions

Mysql – index doesn’t work properly

Mysql – Creating a MySQL Trigger

Related Question