# Oracle SQL Fundamentals/Aggregating Data

Jump to navigation
Jump to search

This lesson introduces aggregating data.

## Objectives and Skills[edit | edit source]

Objectives and skills for the aggregating data portion of Oracle SQL Fundamentals I certification include:^{[1]}

- 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[edit | edit source]

- Read Wikipedia: Aggregate function.
- Read Wikipedia: Group by (SQL).
- Read Wikipedia: Having (SQL).
- Read Oracle: Aggregate Functions.

## Multimedia[edit | edit source]

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

## Activities[edit | edit source]

- Test aggregate functions using HR data.
- Count the total number of employees using the following query:
`SELECT COUNT(*) AS EMPLOYEE_COUNT FROM EMPLOYEES;`

- Count the total number of employees assigned to any department using the following query:
`SELECT COUNT(DEPARTMENT_ID) AS EMPLOYEES_IN_DEPARTMENTS FROM EMPLOYEES;`

- 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'; - 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%';

- Count the total number of employees using the following query:
- Test data grouping using HR data.
- 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; - 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; - 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(*);

- Count the total number of employees assigned to each department using the following query:
- Test restricted grouping using HR data.
- 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; - 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; - 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;

- Count the total number of employees assigned to each department with a count greater than 10 using the following query:
- Test aggregate functions using OE data.
- Determine the total number of orders.
- Determine the total number of orders assigned to a sales rep.
- Determine the average order total.
- Determine the total order amount for all online orders.

- Test data grouping using OE data.
- Determine the total number of orders for each year based on order date.
- Determine the average order amount for each order mode.
- Determine the total number of orders and total order amount for each sales rep, sorted by total order amount in descending order.

- Test restricted grouping using OE data.
- Determine the total number of orders for each year based on order date for dates in 2000 or later.
- Determine the average order amount for each order mode where the order status is 0.
- 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[edit | edit source]

- 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.
^{[2]} - Common aggregate functions include sum (SUM), count, (COUNT), average (AVG), minimum (MIN), and maximum (MAX).
^{[3]} - SUM(expr) returns the sum of values of expr.
^{[4]} - COUNT(expr) returns the number of rows returned by the query.
^{[5]} - AVG(expr) returns the average value of expr.
^{[6]} - MIN(expr) returns the minimum value of expr.
^{[7]} - MAX(expr) returns the maximum value of expr.
^{[8]} - 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.
^{[9]} - 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.
^{[10]} - 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).
^{[11]}

## See Also[edit | edit source]

## References[edit | edit source]