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 Wikipedia: Aggregate function.
2. Read Wikipedia: Group by (SQL).
3. Read Wikipedia: 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.
   1. Count the total number of employees using the following query:
      SELECT COUNT(*) AS EMPLOYEE_COUNT FROM EMPLOYEES;
   2. Count the total number of employees assigned to any department using the following query:
      SELECT COUNT(DEPARTMENT_ID) AS EMPLOYEES_IN_DEPARTMENTS FROM EMPLOYEES;
   3. Determine the average commission of all sales reps using the following query:
      SELECT TO_CHAR(AVG(COMMISSION_PCT), '0.00') || '%' AS AVERAGE_SALES_REP_COMMISSION
FROM EMPLOYEES
WHERE JOB_ID = 'SA_REP';
   4. Determine the total salary of all managers using the following query:
      SELECT TO_CHAR(SUM(SALARY), '$999,990') AS TOTAL_MANAGER_SALARIES
FROM EMPLOYEES
WHERE JOB_ID LIKE '%MGR%' OR JOB_ID LIKE '%MAN%';
2. Test data grouping using HR data.
   1. Count the total number of employees assigned to each department using the following query:
      SELECT DEPARTMENT_ID, COUNT(*) AS EMPLOYEE_COUNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
ORDER BY COUNT(*) DESC;
   2. Determine the average salary for each job using the following query:
      SELECT JOB_ID, TO_CHAR(AVG(SALARY), '$99,990.00') AS AVERAGE_SALARY
FROM EMPLOYEES
GROUP BY JOB_ID
ORDER BY JOB_ID;
   3. Determine the total number of employees and total salary for each department, sorted by total salary in descending order using the following query:
      SELECT DEPARTMENT_ID, COUNT(*) AS EMPLOYEES, TO_CHAR(SUM(SALARY), '$999,990.00') AS TOTAL_SALARY
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
GROUP BY DEPARTMENT_ID
ORDER BY SUM(SALARY) DESC, COUNT(*);
3. Test restricted grouping using HR data.
   1. Count the total number of employees assigned to each department with a count greater than 10 using the following query:
      SELECT DEPARTMENT_ID, COUNT(*) AS EMPLOYEE_COUNT
FROM EMPLOYEES
GROUP BY DEPARTMENT_ID
HAVING COUNT(*) > 10
ORDER BY COUNT(*) DESC;
   2. Determine the average salary for each rep position with an average less than 10,000 using the following query:
      SELECT JOB_ID, TO_CHAR(AVG(SALARY), '$99,990.00') AS AVERAGE_SALARY
FROM EMPLOYEES
WHERE JOB_ID LIKE '%REP%'
GROUP BY JOB_ID
HAVING AVG(SALARY) < 10000
ORDER BY JOB_ID;
   3. 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:
      SELECT MANAGER_ID, TO_CHAR(SUM(SALARY), '$999,990.00') AS TOTAL_SALARY, TO_CHAR(AVG(COMMISSION_PCT), '0.00') || '%' AS AVERAGE_COMMMISSION
FROM EMPLOYEES
WHERE COMMISSION_PCT IS NOT NULL
GROUP BY MANAGER_ID
HAVING AVG(COMMISSION_PCT) >= 0.2
ORDER BY SUM(SALARY) DESC;
4. Test aggregate functions using OE data.
   1. Determine the total number of orders.
   2. Determine the total number of orders assigned to a sales rep.
   3. Determine the average order total.
   4. Determine the total order amount for all online orders.
5. Test data grouping using OE data.
   1. Determine the total number of orders for each year based on order date.
   2. Determine the average order amount for each order mode.
   3. Determine the total number of orders and total order amount for each sales rep, sorted by total order amount in descending order.
6. Test restricted grouping using OE data.
   1. Determine the total number of orders for each year based on order date for dates in 2000 or later.
   2. Determine the average order amount for each order mode where the order status is 0.
   3. 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).

See Also

• Oracle: Database 2 Day Developers Guide - Selecting Table Data

References

← Conditional Expressions

Oracle SQL Fundamentals

Joining Data →