Sales Ledger and Stock Control ER Problem O. - PowerPoint PPT Presentation

1 / 13

[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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

Sales Ledger and Stock Control ER Problem O.

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript

[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;