====== LU06.S03 - SQL-DQL: Selects with Aggregate Functions ======

==== A: MIN ====
We want to know which of our products actually the cheapest is.

  SELECT min(price)
  FROM Products;
  
**Result from DB:**
2.5 



==== B: MAX ====
What is the highest price for the products of the supplier with id = 12?

  SELECT max(price) 
  FROM Products
  WHERE SupplierID = 12;
  
**Result from the DB:** 123.79 

==== c: AVG ====
What is the average price for products of supplier 3?

  SELECT AVG(price) 
  FROM Products
  WHERE SupplierID = 3;
  
**Result from the db:** 31.6667 

==== D: COUNT ====
How many orders do we currently have in our data-base system from the customer with id = 5?

  SELECT count(customerID)
  FROM Orders
  where customerID = 5;
  
**Result from the db:** 3
==== E: SUM ====
What is the worth of the order 10255? Please note, that there are two tables involved in this select statement.

  SELECT sum(price*quantity)
  FROM OrderDetails, Products
  WHERE orderDetails.OrderID = 10255
  AND OrderDetails.ProductID = Products.ProductID;
  
**Result from the db:** 3115.75 

==== F: GROUP BY ====
For our anual report we need list of the orders, and the value of each, grouped by the OrderID.

  SELECT OrderDetails.orderID, sum(price*quantity)
  FROM OrderDetails, Products
  WHERE OrderDetails.ProductID = Products.ProductID
  GROUP BY OrderDetails.orderid;
  
*** Result from the db:**

{{:modul:m290:learningunits:lu04:loesungen:lusdfas..png?600|}}
  
  
===== Vocabulary =====
^ English ^ German ^ 
| ... | ...|


----
[[https://creativecommons.org/licenses/by-nc-sa/4.0/|{{https://i.creativecommons.org/l/by-nc-sa/4.0/88x31.png}}]] Volkan Demir