Assignment
Using the example below based on a dummy table and its values and after watching the video on aggregates answer the following questions:
Various Aggregate Functions
1) Count()
2) Sum()
3) Avg()
4) Min()
5) Max()
Now let us understand each Aggregate function with a example:
Id Name Salary
1 A 80
2 B 40
3 C 60
4 D 70
5 E 60
6 F Null
Count():
Count(*): Returns total number of records .i.e 6.
Count(salary): Return number of Non Null values over the column salary. i.e 5.
Count(Distinct Salary): Return number of distinct Non Null values over the column salary .i.e 4
Sum():
sum(salary): Sum all Non Null values of Column salary i.e., 310
sum(Distinct salary): Sum of all distinct Non-Null values i.e., 250.
Avg():
Avg(salary) = Sum(salary) / count(salary) = 310/5
Avg(Distinct salary) = sum(Distinct salary) / Count(Distinct Salary) = 250/4
Min():
Min(salary): Minimum value in the salary column except NULL i.e., 40.
Max(salary): Maximum value in the salary i.e., 80.
/* Question 1: Select the total number of Products in the Products table */
/* Question 2: Select the total number of Shippers for Orders (Hint: use COUNT(ShipVia) as TotalProducts to return non-null counts. (You will notice that by doing a select * from Orders there are ShipVia foreign keys as NULLs. As a result the count will exclude it while counting.) */
/* Question 3: Select the total number of DISTINCT Shippers for Orders (Hint: use COUNT(Distinct ShipVia) to return non-null counts. (You will notice that by doing a select * from Orders there are ShipVia foreign keys as NULLs and there are duplicate ShipVia. As a result the count distinct will exclude both while counting.) */
/* Question 4: Select all non-null total price from Order Details where OrderID = 10248 (Hint Use SUM(UnitPrice * Quantity) as OrderTotal) */
/* Question 5: Select all non-null distinct non-null freight charges from the Orders table (Hint: Use SUM(DISTINCT Freight) as TotalFreight where CustomerID = 'VINET' */
/* Question 6: Select all non-null distinct total products that were ordered from the Order Details table (Hint: Use COUNT(DISTINCT ProductID) as TotalProducts */
/* Question 7: Select average unitprice of Products */
/* Question 8: Select ProductName and UnitPrice pf Products that have an above average price:
(Hint: Use a sub-query similar to one done during class exercise to first write sub-query to select Average UnitPrice of Products and then using a where clause (UnitPrice > (subquery)) as part of the outer query select ProductName and UnitPrice)
*/
/*
Question 9a: Select Product having the maximum UnitPrice
Question 9b: Select Product having the minimum UnitPrice
*/
/* Question 10: Select CompanyName, count(OrderID) as NumberOfOrders grouped by shippers
(Hint: Use inner join to join Orders and Shippers and then use Group By CompanyName refer to the video and lecture notes)
*/