Skip to main content

Intermediate SQL

GROUP BY and HAVING

When working with large datasets, you often need to aggregate data. For example, calculate totals, averages, or counts for groups of records. GROUP BY and HAVING are SQL clauses that help you summarize and filter grouped data.

GROUP vs Having

Clause	Purpose
GROUP BY	Aggregate rows into groups.
HAVING	Filter aggregated groups.

GROUP BY

Groups rows that share the same value in one or more columns. Aggregate functions like SUM, COUNT, AVG, MIN, and MAX are typically used with GROUP BY.

Count students per course

SELECT course_id, COUNT(*) AS student_count
FROM enrollments
GROUP BY course_id;

Explanation:
- GROUP BY course_id groups all enrollment records by course.
- COUNT(*) calculates the number of students in each course.

Total sales per department

SELECT department_id, SUM(sales_amount) AS total_sales
FROM employees
GROUP BY department_id;

Explanation:
- GROUP BY department_id groups all employee records by department.
- SUM(sales_amount) will total all the sales_amount per department.

HAVING

While WHERE filters individual rows, HAVING filters groups after aggregation.

Only show courses with more than 10 students

SELECT course_id, COUNT(*) AS student_count
FROM enrollments
GROUP BY course_id
HAVING COUNT(*) > 10;

Departments with total sales above 50,000

SELECT department_id, SUM(sales_amount) AS total_sales
FROM employees
GROUP BY department_id
HAVING SUM(sales_amount) > 50000;

WHERE vs HAVING clause

WHERE: Filters rows before grouping.
HAVING: Filters groups after aggregation.

Aggregate Functions

Perform calculations on a set of rows and return a single value. They are commonly used with GROUP BY to summarize data, but can also be used on the entire table.

SUM

Calculates the sum of a column for a set of rows.

Total sales

SELECT SUM(sales_amount) AS total_sales
FROM employees;

Total sales per department

SELECT department_id, SUM(sales_amount) AS total_sales
FROM employees
GROUP BY department_id;

COUNT

Counts the number of rows or non-null values.

Total number of students

SELECT COUNT(*) AS total_students
FROM students;

Total number of students per course

SELECT course_id, COUNT(*) AS student_count
FROM enrollments
GROUP BY course_id;

AVG

Calculates the average of numeric values.

Average employee salary

SELECT AVG(salary) AS avg_salary
FROM employees;

Average salary per department

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;

MIN

Finds the smallest value in a column.

Lowest price product

SELECT MIN(price) AS lowest_price
FROM products;

Lowest price product per category

SELECT category_id, MIN(price) AS lowest_price
FROM products
GROUP BY category_id;

MAX

Finds the largest value in a column.

Highest salary

SELECT MAX(salary) AS highest_salary
FROM employees;

Maximum sales per department

SELECT department_id, MAX(sales_amount) AS max_sales
FROM employees
GROUP BY department_id;

tip

Aggregate functions ignore NULL values except for COUNT(*).
Combine with GROUP BY to summarize per category.
Use HAVING to filter results based on aggregates or grouping. For instanace departments with total sales > 50,000.

CASE Expressions

Allows you to perform conditional logic in SQL queries, similar to "if-then-else" statements in programming. It can be used in SELECT, WHERE, ORDER BY, and other clauses to return values based on conditions.

Simple CASE Expression

Checks a column or expression against specific values.

Assign letter grades based on marks

SELECT student_name, mark,
       CASE mark
           WHEN 90 THEN 'A'
           WHEN 80 THEN 'B'
           WHEN 70 THEN 'C'
           ELSE 'F'
       END AS grade
FROM students;

Explanation:
- CASE mark compares the mark to each value.
- Returns the corresponding grade, or 'F' if no match.

Searched CASE Expression

Uses conditions instead of exact values, allowing more complex logic.

Pass / Fail based on mark

SELECT student_name, mark,
       CASE
           WHEN mark >= 60 THEN 'Pass'
           ELSE 'Fail'
       END AS result
FROM students;

Explanation:
- Each WHEN clause contains a condition. For instance: mark >= 60.
- Returns 'Pass' if the score is 60 or above, 'Fail' otherwise.

Using CASE with Aggregate Functions

Is often combined with aggregates to calculate conditional summaries.

Count students who passed vs failed

SELECT
    SUM(CASE WHEN mark >= 60 THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN mark < 60 THEN 1 ELSE 0 END) AS failed
FROM students;

Explanation:
- Counts the number of passing and failing students using SUM and CASE.
- First it determines the value based on the CASE statement, then it's resulting value is added up.

Case expression tips

CASE returns a single value for each row.
Always end the CASE with END.
Can be nested or used in multiple columns.
Works in SELECT, WHERE, ORDER BY, and GROUP BY.

Subqueries (IN, EXISTS)

A SQL query nested inside another query. Subqueries allow you to use the result of one query to filter, compare, or calculate data in another query.
They are very useful for solving complex problems without joining tables unnecessarily.

Subquery with IN

The IN operator checks if a value matches any value returned by a subquery.

Students enrolled in a specific course

SELECT student_name
FROM students
WHERE student_id IN (
    SELECT student_id
    FROM enrollments
    WHERE course_id = 101
);

Explanation:
- The inner query selects all student_ids enrolled in course 101.
- The outer query returns names of students whose ids are in that list.

Products in certain categories

SELECT product_name
FROM products
WHERE category_id IN (
    SELECT category_id
    FROM categories
    WHERE category_name = 'Electronics'
);

Explanation:
- The inner query selects category_id with name equal to Electronics.
- The outer query returns names of products whose ids are in that list.

Subquery with EXISTS

The EXISTS operator checks whether the subquery returns at least one row. It returns TRUE or FALSE.

Students who are enrolled in any course

SELECT student_name
FROM students s
WHERE EXISTS (
    SELECT 1
    FROM enrollments e
    WHERE e.student_id = s.student_id
);

Explanation:
- EXISTS returns TRUE if the inner query finds at least one matching row.
- Only students with at least one enrollment are returned.

Departments with employees earning more than 50,000

SELECT department_name
FROM departments d
WHERE EXISTS (
    SELECT 1
    FROM employees e
    WHERE e.department_id = d.department_id
      AND e.salary > 50000
);

Explanation:
- EXISTS returns TRUE if the inner query finds at least one matching row.
- Only employees earning more than 50,000 are returned.

IN vs EXISTS

Feature	IN	EXISTS
Checks	Value against a list of results	Whether subquery returns rows
Performance	Good for small lists	Better for correlated subqueries with many rows
Returns	TRUE if value matches	TRUE if at least one row exists

GROUP BY and HAVING
- GROUP BY
- HAVING
Aggregate Functions
- SUM
- COUNT
- AVG
- MIN
CASE Expressions
Subqueries (IN, EXISTS)