SQL Having

Example Table

We will use the following table named sales for our examples:

    CREATE TABLE sales (
        sale_id INT PRIMARY KEY,
        product_name VARCHAR(50),
        sale_date DATE,
        amount DECIMAL(10, 2)
    );

    INSERT INTO sales (sale_id, product_name, sale_date, amount) VALUES
    (1, 'Product A', '2023-01-01', 100.00),
    (2, 'Product B', '2023-01-02', 150.00),
    (3, 'Product A', '2023-01-03', 200.00),
    (4, 'Product C', '2023-01-04', 250.00),
    (5, 'Product B', '2023-01-05', 300.00);
    

Sales Table

Using SQL HAVING

The SQL HAVING clause is used to filter records that work on summarized GROUP BY results. It is similar to the WHERE clause but is used for groups rather than individual rows.

Example 1: Total Sales Amount by Product with HAVING

To calculate the total sales amount for each product and filter products with total sales greater than 200, use the following query:

    SELECT product_name, SUM(amount) AS total_amount
    FROM sales
    GROUP BY product_name
    HAVING SUM(amount) > 200;
    

Result:

Example 2: Average Sales Amount by Product with HAVING

To calculate the average sales amount for each product and filter products with an average sales amount greater than 200, use the following query:

    SELECT product_name, AVG(amount) AS average_amount
    FROM sales
    GROUP BY product_name
    HAVING AVG(amount) > 200;
    

Result: