SQL code copied to buffer

SQLtest.online
  1. 1.  Introduction to Databases
  2. 2.  Types of Databases
  3. 3.  Relational Database Concepts
  4. 4.  Basic Data Types
  5. 5.  Understanding NULL Values in SQL
  6. 6.  Overview of SQL
  1. 1.  Select Data from a Table
  2. 2.  Filtering Data
  3. 3.  Combine Multiple Conditions
  4. 4.  Aliasing Columns
  5. 5.  Ordering Results
  6. 6.  Limiting Results with LIMIT and OFFSET
  7. 7.  Putting It All Together: WHERE, ORDER BY, and LIMIT
  1. 1.  Built-in SQL Functions
  2. 2.  Common String Functions
  3. 3.  Core Mathematical Functions
  4. 4.  Date and Time Functions
  5. 5.  Conditional Operator
  1. 1.  Basic Aggregation Functions
  2. 2.  Grouping Data
  3. 3.  Filtering Grouped Data
  4. 4.  Conditional Aggregation
  5. 5.  Advanced Aggregation
  1. 1.  Fundamentals of JOINs in SQL
  2. 2.  INNER JOIN - Combining Matching Rows
  3. 3.  LEFT JOIN - Including All Records from the Left Table
  4. 4.  RIGHT JOIN - Including All Records from the Right Table
  5. 5.  FULL OUTER JOIN - Combining Everything from Both Tables
  6. 6.  CROSS JOIN - The Cartesian Product
  7. 7.  SELF JOIN - Joining a Table to Itself
  8. 8.  Practical JOIN Scenarios and Techniques
  9. 9.  Join Algorithms
  1. 1.  Introduction to Subqueries - Nested Queries and Inline Views
  2. 2.  Subqueries in the WHERE Clause
  3. 3.  Correlated Subqueries
  4. 4.  Common Table Expressions (CTEs)
  5. 5.  Recursive CTEs
  6. 6.  Applying Recursive CTEs
  1. 1.  Window Functions
  2. 2.  Using ROW_NUMBER, RANK, DENSE_RANK, and NTILE
  3. 3.  Window Frames — Controlling the Window Boundaries
  1. 1.  The INSERT INTO Statement
  2. 2.  The UPDATE Statement
  3. 3.  The DELETE Statement
  1. 1.  The CREATE TABLE Statement
  2. 2.  The TRUNCATE and DROP TABLE Statements
  3. 3.  Temporary Tables
  4. 4.  Views
  1. 1.  Best Practices for Readable and Maintainable SQL Code
  2. 2.  Writing Efficient SQL Queries
  3. 3.  Understanding Query Optimization Methods
  1. 1.  The fastest flight option
  2. 2.  Calculate the average occupancy rate of flights
  3. 3.  Aircraft Seat Map

Lesson 4.3: Filtering Grouped Data with HAVING in SQL

When working with grouped data in SQL, you often need to filter the results of aggregation. The HAVING clause allows you to specify conditions on groups created by the GROUP BY clause, similar to how WHERE filters individual rows. In this lesson, you will learn how to use HAVING to filter aggregated results, with practical examples from the Sakila database.

The Role of HAVING

  • WHERE filters rows before grouping.
  • HAVING filters groups after aggregation. HAVING is most commonly used with aggregate functions, but it can also be applied to non-aggregated columns (usually columns from GROUP BY).

Syntax

SELECT column1, AGG_FUNCTION(column2)
FROM table
GROUP BY column1
HAVING condition;

Example: Customers with Total Payments Above a Threshold

SELECT customer_id, SUM(amount) AS total_paid
FROM payment
GROUP BY customer_id
HAVING SUM(amount) > 100;

Result: Returns only those customers whose total payments exceed 100.

Example: Employees with More Than 50 Payments Processed

SELECT staff_id, COUNT(*) AS payments_count
FROM payment
GROUP BY staff_id
HAVING COUNT(*) > 50;

Result: Shows only staff members who processed more than 50 payments.

Example: Filtering by Average Payment Amount

SELECT customer_id, AVG(amount) AS avg_payment
FROM payment
GROUP BY customer_id
HAVING AVG(amount) >= 5;

Result: Lists customers whose average payment is at least 5.

Using HAVING with Multiple Conditions

You can combine multiple conditions in the HAVING clause using AND/OR.

SELECT staff_id, COUNT(*) AS payments_count, SUM(amount) AS total_paid
FROM payment
GROUP BY staff_id
HAVING COUNT(*) > 50 AND SUM(amount) > 500;

Result: Returns staff who processed more than 50 payments and whose total payments exceed 500.

Practical Usage

  1. Top-Selling Film Categories:

    SELECT c.name AS category, SUM(p.amount) AS total_sales
FROM payment p
JOIN rental r ON p.rental_id = r.rental_id
JOIN inventory i ON r.inventory_id = i.inventory_id
JOIN film f ON i.film_id = f.film_id
JOIN film_category fc ON f.film_id = fc.film_id
JOIN category c ON fc.category_id = c.category_id
GROUP BY c.name
HAVING SUM(p.amount) > 2000;

    Shows only categories with total sales above 2000.

  2. Countries with More Than 20 Customers:

    SELECT country, COUNT(*) AS customers_count
FROM customer cu
JOIN address a ON cu.address_id = a.address_id
JOIN city ci ON a.city_id = ci.city_id
JOIN country co ON ci.country_id = co.country_id
GROUP BY country
HAVING COUNT(*) > 20;

    Lists countries with more than 20 customers.

Key Takeaways from This Lesson

  • Use HAVING to filter groups after aggregation.
  • HAVING works with aggregated data, while WHERE does not.
  • Combine HAVING with GROUP BY for powerful data analysis and reporting.

Practice using HAVING with your own queries to gain deeper insights from grouped data in SQL.