Team 7 chase younger kristin hamilton santiago paiz
This presentation is the property of its rightful owner.
Sponsored Links
1 / 13

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


  • 89 Views
  • Uploaded on
  • Presentation posted in: General

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

Download Presentation

Sales Ledger and Stock Control ER Problem O.

An Image/Link below is provided (as is) to download presentation

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

[Team 7]

Chase Younger ▪ Kristin Hamilton ▪ Santiago Paiz

Sales Ledger and Stock ControlER Problem O.


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

Relevant tables


What is the biggest supplier quantity

“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

▪ 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

▪ 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

▪ 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

▪ 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

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


Sales ledger and stock control er problem o

▪ 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

▪ 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

Example using MySQL user-created variables

SELECT SUM(quantityOrdered*sale_price)

INTO @salesTotal

FROM productOrders;

SELECT SUM(quantity*stock_price)

[email protected]

FROM Restock;

SELECT FLOOR(@[email protected]) AS productSalesProfit;


End of team 7 presentation

end of Team 7 presentation


  • Login