Oracle SQL Fundamentals/Aggregating Data

This lesson introduces aggregating data.

Objectives and Skills
Objectives and skills for the aggregating data portion of Oracle SQL Fundamentals I certification include:
 * Reporting Aggregated Data Using the Group Functions
 * Identify the available group functions
 * Describe the use of group functions
 * Group data by using the GROUP BY clause
 * Include or exclude grouped rows by using the HAVING clause

Readings

 * 1) Read  Aggregate function.
 * 2) Read  Group by (SQL).
 * 3) Read  Having (SQL).
 * 4) Read Oracle: Aggregate Functions.

Multimedia

 * 1) YouTube: Oracle SQL Tutorial - Querying a table - Part 11a) - Group Functions (Theory)
 * 2) YouTube: Oracle SQL Tutorial - Querying a Table - Part 11(b) - Group Functions (Hands-on)

Activities

 * 1) Test aggregate functions using HR data.
 * 2) Count the total number of employees using the following query:
 * 3) Count the total number of employees assigned to any department using the following query:
 * 4) Determine the average commission of all sales reps using the following query:
 * 5) Determine the total salary of all managers using the following query:
 * 6) Test data grouping using HR data.
 * 7) Count the total number of employees assigned to each department using the following query:
 * 8) Determine the average salary for each job using the following query:
 * 9) Determine the total number of employees and total salary for each department, sorted by total salary in descending order using the following query:
 * 10) Test restricted grouping using HR data.
 * 11) Count the total number of employees assigned to each department with a count greater than 10 using the following query:
 * 12) Determine the average salary for each rep position with an average less than 10,000 using the following query:
 * 13) Determine the total salary and average commission for each employee reporting to the same manager, sorted by total salary in descending order. List only those managers with employees having an average commission greater than or equal to 0.20 using the following query:
 * 14) Test aggregate functions using OE data.
 * 15) Determine the total number of orders.
 * 16) Determine the total number of orders assigned to a sales rep.
 * 17) Determine the average order total.
 * 18) Determine the total order amount for all online orders.
 * 19) Test data grouping using OE data.
 * 20) Determine the total number of orders for each year based on order date.
 * 21) Determine the average order amount for each order mode.
 * 22) Determine the total number of orders and total order amount for each sales rep, sorted by total order amount in descending order.
 * 23) Test restricted grouping using OE data.
 * 24) Determine the total number of orders for each year based on order date for dates in 2000 or later.
 * 25) Determine the average order amount for each order mode where the order status is 0.
 * 26) Determine the total number of orders and total order amount for each sales rep, sorted by total order amount in descending order. List only those orders sold by sales reps having more than 10 sales.

Lesson Summary

 * An aggregate function is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning or measurement.
 * Common aggregate functions include sum (SUM), count, (COUNT), average (AVG), minimum (MIN), and maximum (MAX).
 * SUM(expr) returns the sum of values of expr.
 * COUNT(expr) returns the number of rows returned by the query.
 * AVG(expr) returns the average value of expr.
 * MIN(expr) returns the minimum value of expr.
 * MAX(expr) returns the maximum value of expr.
 * The GROUP BY clause selects rows having common values into a smaller set of rows. GROUP BY is often used in conjunction with SQL aggregation functions or to eliminate duplicate rows from a result set. The WHERE clause is applied before the GROUP BY clause.
 * The HAVING clause includes a predicate used to filter rows resulting from the GROUP BY clause. Because it acts on the results of the GROUP BY clause, aggregation functions can be used in the HAVING clause predicate.
 * The full syntax of the SELECT statement is SELECT column(s) FROM table(s) WHERE condition(s) GROUP BY column(s) HAVING condition(s) ORDER BY column(s).