DbSchema Database Designer

DbSchema Tutorial | SQL HAVING CLAUSE

Publish Article >>>

Structured Query Language (SQL) is a potent tool for handling relational databases. One of its vital features is the HAVING clause, which allows us to filter results after performing an aggregate function. This article delves deep into the world of the HAVING clause, distinguishing it from WHERE, and showcasing its applications with various aggregate functions.

1. Introduction to SQL HAVING CLAUSE

The HAVING clause is used in SQL to filter the results of aggregate functions like SUM, AVG, MAX, etc. Unlike the WHERE clause, which filters rows before aggregation, HAVING filters after the aggregation is completed.

Syntax:

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING aggregate_function(column2) condition;

2. Differentiate between SQL HAVING vs WHERE

Attribute SQL HAVING SQL WHERE
Purpose Filters aggregated data Filters individual rows
Use with Typically with GROUP BY Used without GROUP BY (though can be used with)
Position in SQL Comes after GROUP BY Comes before GROUP BY

3. Using Aggregate Functions with HAVING

a. Using SUM Function

Example: Let’s consider a sample table Orders:

OrderID ProductName Quantity
1 Apples 50
2 Bananas 30
3 Apples 20

If we want to find products that have a total quantity greater than 40:

SELECT ProductName, SUM(Quantity) as TotalQuantity
FROM Orders
GROUP BY ProductName
HAVING SUM(Quantity) > 40;

Result:

ProductName TotalQuantity
Apples 70

b. Using MIN Function

Finding products with the minimum order quantity less than 25:

SELECT ProductName, MIN(Quantity) as MinimumQuantity
FROM Orders
GROUP BY ProductName
HAVING MIN(Quantity) < 25;

Result:

ProductName MinimumQuantity
(No results matching the criteria)

c. Using AVG Function

Finding products with an average quantity below 30:

SELECT ProductName, AVG(Quantity) as AverageQuantity
FROM Orders
GROUP BY ProductName
HAVING AVG(Quantity) < 30;

Result:

ProductName AverageQuantity
Bananas 30

d. Using MAX Function

Finding products with a maximum order quantity greater than 60:

SELECT ProductName, MAX(Quantity) as MaximumQuantity
FROM Orders
GROUP BY ProductName
HAVING MAX(Quantity) > 60;

Result:

ProductName MaximumQuantity
(No results matching the criteria)

4. HAVING with GROUP BY and ORDER BY Clauses

a. Filtering Rows Using WHERE and HAVING

Filtering products with a total quantity greater than 20 and grouping those with a total quantity greater than 40:

SELECT ProductName, SUM(Quantity) as TotalQuantity
FROM Orders
WHERE Quantity > 20
GROUP BY ProductName
HAVING SUM(Quantity) > 40;

b. Filtering Rows on Multiple Values Using HAVING

Finding products with a total quantity between 30 and 70:

SELECT ProductName, SUM(Quantity) as TotalQuantity
FROM Orders
GROUP BY ProductName
HAVING SUM(Quantity) BETWEEN 30 AND 70;

Result:

ProductName TotalQuantity
Apples 70
Bananas 30

c. HAVING with GROUP BY Clause

Grouping by product name and filtering products with a total quantity greater than 40:

SELECT ProductName, SUM(Quantity) as TotalQuantity
FROM Orders
GROUP BY ProductName
HAVING SUM(Quantity) > 40;

Result:

ProductName TotalQuantity
Apples 70

d. HAVING with ORDER BY Clause

Ordering the grouped results by total quantity in descending order:

SELECT ProductName, SUM(Quantity) as TotalQuantity
FROM Orders
GROUP BY ProductName
HAVING SUM(Quantity) > 40
ORDER BY TotalQuantity DESC;

Result:

ProductName TotalQuantity
Apples 70

e. HAVING Clause with COUNT() Function

Finding products that have been ordered more than once:

SELECT ProductName, COUNT(OrderID) as NumberOfOrders
FROM Orders
GROUP BY ProductName
HAVING COUNT(OrderID) > 1;

Result:

ProductName NumberOfOrders
Apples 2

5. Common Mistakes & FAQs

Mistakes

  1. Using HAVING without GROUP BY:

    • The HAVING clause is typically used to filter aggregated results. Without the GROUP BY clause, there’s often little reason to use HAVING. While some databases might allow this, it’s a misuse of the clause’s intent.
  2. Confusing the order of SQL operations:

    • It’s crucial to remember the sequence: FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY. Trying to filter aggregated results using WHERE instead of HAVING will throw an error.
  3. Using non-aggregated columns in the HAVING clause without them being in the GROUP BY clause:

    • This is a common error. If you’re trying to use a column in the HAVING clause that’s not part of an aggregate function, it must also appear in the GROUP BY clause.
  4. Over-relying on HAVING:

    • While HAVING is powerful, it shouldn’t replace WHERE for basic row-level filtering. Use WHERE wherever possible to reduce the size of the result set early in the processing.

FAQs

  1. Can we use WHERE and HAVING together?

    • Yes, and it’s common to do so. WHERE filters rows before aggregation, while HAVING filters after aggregation.
  2. Why is my HAVING clause not recognizing a column that’s in my SELECT statement?

    • This typically occurs when trying to filter on a column that’s not a part of an aggregate function or the GROUP BY clause. If you’re grouping results, every column in the SELECT that’s not part of an aggregate function needs to be included in the GROUP BY clause.
  3. Can I use aggregate functions in the WHERE clause instead of HAVING?

    • No, aggregate functions cannot be used in the WHERE clause. If you need to filter based on the result of an aggregate function, you should use the HAVING clause.
  4. Is HAVING exclusive to SQL?

    • While the concept of filtering on aggregated data is not unique to SQL, the HAVING clause, as described in this article, is SQL-specific.
  5. Can I use HAVING without any aggregate functions?

    • Technically, yes, some databases might allow this, but it’s not standard practice. The purpose of HAVING is to filter aggregated results, so using it without aggregations can be confusing and misrepresents its intent.

6. Practice Questions

  1. Write a SQL statement to find products that have an average quantity below 30.
  2. Find the minimum order quantity for each product, but only display products with a minimum order of more than 20.
  3. How would you list products that have been ordered more than 3 times, based on the Orders table?

By integrating comprehensive explanations with practical examples, we aim to offer an in-depth understanding of the SQL HAVING clause. Remember, practice is key. Experiment with different scenarios and datasets to refine your SQL skills.

Happy Querying

Visually Manage Databases using DbSchema

DbSchema is a databases client and visual designer. DbSchema has a free Community Edition, which can be downloaded here.
DbSchema main features include:

DbSchema Designer alt >

Interactive Diagrams

Design tables, column and foreign keys directly in diagrams, by double-clicking them. Changes will be saved to the design model and, if DbSchema is connected to the database also into the database. More.


Connection Dialog alt >

Simple Connection Dialog

Choose the database location, the user and password, and simply get connected. Choose 'Edit Manually' into the JDBC URL combo to enter a custom URL. More.


Relational Data Explorer alt >

Relational Data Explorer

Explore data from multiple tables simultaneously, using foreign keys or virtual foreign keys. Double-click cells to edit the data. More.


Query Builder alt >

Query Builder

Create SQL Queries featuring JOINS, GROUP BY, ORDER BY just using the mouse. More.


SQL Query Editor alt >

SQL Query Editor

Edit and execute SQL Queries. The editor is autocompletion-enabled. More.


Schema Synchronization alt >

Design Schema in Team & Schema Deployment

DbSchema is using the design model, a copy of the schema structure, independent of the database.
The design model can be saved to file and shared in a team.
Connecting to another database you may compare the design model with the database, commit the differences or merge them in the design model. More.


Dark Theme alt >

Dark Theme

Configurable styles & dark theme. More.


Many features are available in the free Community edition.
The Pro edition adds capabilities to save the design to the model file, design schema in team and deploy the schema on multiple databases.


DbSchema can be downloaded for free. No registration is required.