Assignment 5 (Aggregate Functions)

SQL Aggregate Functions: GROUP BY, HAVING & Real Query Examples

Topic: Mastering aggregate functions like AVG, SUM, MAX, MIN, and COUNT with GROUP BY and HAVING in Oracle SQL.

If you've ever needed to summarize data — find the highest salary, count employees per department, or calculate average scores — SQL aggregate functions are your go-to tools. This guide walks you through every major aggregate function with real queries and expected outputs, so you can revise fast and write confidently.

What Are Aggregate Functions in SQL?

Aggregate functions perform a calculation on a set of rows and return a single value. They're widely used in data analysis, reporting dashboards, and backend business logic.

Here's a quick reference table before we dive into the queries:

GROUP BY and HAVING: The Power Duo

Before jumping into queries, two clauses you'll always see alongside aggregate functions:

• GROUP BY — groups rows that share the same value in a column (e.g., all employees in the same department).

• HAVING — filters groups after aggregation. Think of it as WHERE, but for grouped results.

💡 Quick rule: Use WHERE to filter rows before grouping. Use HAVING to filter after grouping.

Assignment 5 — All Queries With Expected Output

Query 1: Minimum, Maximum, and Average Salary

SELECT MIN(salary) AS "MIN_SALARY",
       MAX(salary) AS "MAX_SALARY",
       AVG(salary) AS "AVG_SALARY"
FROM employees;

Expected Output:

Query 2: Count of Distinct Job Titles

SELECT COUNT(DISTINCT job_id) AS "JOB_TITLES"
FROM employees;

Expected Output:

Query 3: Difference Between Maximum and Minimum Salary

SELECT (MAX(salary) - MIN(salary)) AS "SALARY_DIFFERENCE"
FROM employees;

Expected Output:

Query 4: Total Salary Expense of the Company

SELECT SUM(salary) AS "TOTAL_SALARY_EXPENSE"
FROM employees;

Expected Output:

Query 5: Average Salary of All Employees

SELECT AVG(salary) AS "AVERAGE_SALARY"
FROM employees;

Expected Output:

Query 6: Total Records in the Employee Table

SELECT COUNT(*) AS "TOTAL_RECORDS"
FROM employees;

Expected Output:

Query 7: Number of Employees in Each Department

SELECT department_id,
       COUNT(employee_id) AS "NO_OF_EMPLOYEES"
FROM employees
GROUP BY department_id
ORDER BY department_id;

Expected Output (partial):

Query 8: Max and Min Salary Per Department

SELECT department_id,
       MAX(salary) AS "MAX_SAL",
       MIN(salary) AS "MIN_SAL"
FROM employees
GROUP BY department_id;

Expected Output (partial):

Query 9: Jobs With Fewer Than 3 Employees (HAVING)

SELECT job_id,
       COUNT(*) AS "EMP_COUNT"
FROM employees
GROUP BY job_id
HAVING COUNT(*) < 3;

Expected Output (partial):

Query 10: Departments Where Max Salary Exceeds 10,000

SELECT department_id,
       AVG(salary) AS "AVG_SALARY"
FROM employees
GROUP BY department_id
HAVING MAX(salary) > 10000;

Expected Output:

Query 11: Job-wise Total Salary (Excluding Managers, Total > 13000)

SELECT job_id,
       SUM(salary) AS "TOTAL_SALARY"
FROM employees
WHERE job_id NOT LIKE '%MAN%'
GROUP BY job_id
HAVING SUM(salary) > 13000
ORDER BY TOTAL_SALARY;

Expected Output:

Common Mistakes to Avoid

• Using WHERE instead of HAVING for aggregate conditions — this will throw an error in Oracle SQL.

• Forgetting GROUP BY when selecting non-aggregate columns alongside aggregate functions.

• Misusing COUNT(column) vs COUNT(*) — COUNT(column) ignores NULLs, COUNT(*) counts everything.

Also Explore These Topics

Looking to build on this knowledge? Here are related notes worth checking out:

• SQL Quick Recap — ~10 mins read

• SQL Triggers — Automating Actions on Data Changes

• SQL Assignment 4 — Single Row Functions

• SQL Assignment 6 — Joins

• SQL Assignment 7 — SQL / DDL / Constraints

• DBMS Overview — Foundations Before SQL

• Data Science Basics — Where SQL Meets Analysis

High authority articles

• Oracle Official Docs — Aggregate Functions

• W3Schools — SQL GROUP BY Statement

• GeeksforGeeks — HAVING vs WHERE in SQL