team 7 chase younger kristin hamilton santiago paiz
Download
Skip this Video
Download Presentation
Sales Ledger and Stock Control ER Problem O.

Loading in 2 Seconds...

play fullscreen
1 / 13

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


  • 113 Views
  • Uploaded on

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

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

PowerPoint Slideshow about 'Sales Ledger and Stock Control ER Problem O.' - brinly


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.

slide2
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
“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.

slide10
▪ 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;

ad