SQL HAVING Clause Overview

The SQL HAVING clause is used to filter records after aggregation operations. Unlike the WHERE clause, which filters rows before aggregation, HAVING filters the results of aggregate functions like SUM(), COUNT(), AVG(), etc. It is used in conjunction with the GROUP BY clause to filter groups of records.

HAVING Syntax

The syntax for the HAVING clause is:

SELECT column_name(s), aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s)
HAVING aggregate_function(column_name) condition;

SQL HAVING Clause Example

To illustrate the HAVING clause, let’s use the following unique tables:

Orders Table

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)
VALUES
(1, 101, '2024-01-10', 250.00),
(2, 102, '2024-01-15', 450.00),
(3, 101, '2024-02-20', 200.00),
(4, 103, '2024-02-25', 150.00),
(5, 102, '2024-03-01', 300.00),
(6, 104, '2024-03-15', 500.00);

Using HAVING to Filter Aggregated Results

To find customers who have made more than one order with a total amount greater than $400, use the following query:

SELECT 
    CustomerID, 
    COUNT(OrderID) AS NumberOfOrders, 
    SUM(Amount) AS TotalAmount
FROM 
    Orders
GROUP BY 
    CustomerID
HAVING 
    COUNT(OrderID) > 1 AND SUM(Amount) > 400;

In this query:

• COUNT(OrderID) counts the number of orders per customer.
• SUM(Amount) calculates the total amount spent by each customer.
• HAVING COUNT(OrderID) > 1 AND SUM(Amount) > 400 filters the results to include only those customers who have made more than one order and spent more than $400 in total.

Using HAVING with Aggregate Functions

To find products that have an average price greater than $300, assuming we have a Products table:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(50),
    Category VARCHAR(50),
    Price DECIMAL(10, 2)
);

INSERT INTO Products (ProductID, ProductName, Category, Price)
VALUES
(1, 'Laptop', 'Electronics', 1200.00),
(2, 'Smartphone', 'Electronics', 800.00),
(3, 'Tablet', 'Electronics', 450.00),
(4, 'Headphones', 'Accessories', 150.00),
(5, 'Monitor', 'Electronics', 300.00);

SELECT 
    Category, 
    AVG(Price) AS AveragePrice
FROM 
    Products
GROUP BY 
    Category
HAVING 
    AVG(Price) > 300;

In this query:

• AVG(Price) calculates the average price per category.
• HAVING AVG(Price) > 300 filters categories with an average price greater than $300.

Important Points About SQL HAVING Clause

• The HAVING clause is used to filter results after aggregation.
• It can be used with aggregate functions like SUM(), COUNT(), AVG(), etc.
• The HAVING clause is placed after the GROUP BY clause.
• The HAVING clause can include multiple conditions connected with logical operators like AND and OR.

SQL HAVING Clause with Aliases Example

Using table aliases can simplify queries, especially when dealing with multiple tables:

SELECT 
    o.CustomerID, 
    COUNT(o.OrderID) AS NumberOfOrders, 
    SUM(o.Amount) AS TotalAmount
FROM 
    Orders o
GROUP BY 
    o.CustomerID
HAVING 
    COUNT(o.OrderID) > 1 AND SUM(o.Amount) > 400;