Download
1 / 13
130 likes | 260 Views
[Team 7] Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz. Sales Ledger and Stock Control ER Problem O. Breakdown of one of our lab9 reports showing effect of removing or modifying certain statements from query. (ORDER BY, GROUP BY, SUM()). Relevant tables.
E N D
[Team 7] Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz Sales Ledger and Stock ControlER Problem O.
Breakdown of one of our lab9 reports showing effect of removing or modifying certain statements from query (ORDER BY, GROUP BY, SUM())
“What is the biggest supplier – quantity” SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY totalQtydesc; largest totalQty * smallest totalQty
▪ ORDER BY (1 of 3) SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY totalQtydesc;
▪ ORDER BY (2 of 3) SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY totalQtydesc; smallest totalQty largest totalQty
▪ ORDER BY (3 of 3) SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY S.name;
▪ GROUP BY SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY totalQtydesc;
▪ GROUP BY(contd) SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY totalQtydesc; = 500 + 380 + 350 + 315 + 50 + 20 + 3.
▪ SUM() SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY R.quantitydesc;
▪ GROUP BY, SUM() SELECT R.supplier, S.name, SUM(R.quantity) totalQty FROM Restock R, Suppliers S WHERE R.supplier = S.suppID GROUP BY R.supplier ORDER BY R.quantitydesc;
Example using MySQL user-created variables SELECT SUM(quantityOrdered*sale_price) INTO @salesTotal FROM productOrders; SELECT SUM(quantity*stock_price) INTO@restockTotal FROM Restock; SELECT FLOOR(@salesTotal-@restockTotal) AS productSalesProfit;
