SQL HAVING Clause

SQL HAVING Clause

The SQL HAVING clause is used to filter records that have been grouped by the GROUP BY clause. This command is essential for applying conditions to aggregated data, enabling more refined and meaningful query results.

Syntax

SELECT column1, aggregate_function(column2)
FROM table_name
GROUP BY column1
HAVING condition;
  • SELECT column1, aggregate_function(column2): This specifies the columns to retrieve and the aggregate function to apply, such as SUM, COUNT, AVG, etc.
  • FROM table_name: This specifies the name of the table from which to retrieve the data.
  • GROUP BY column1: This groups the result set by one or more columns.
  • HAVING condition: This applies a condition to the grouped data.

Example

Let's go through a complete example that includes creating a database, creating a table, inserting data into the table, and then using the HAVING clause to filter grouped data.

Step 1: Creating a Database

This step involves creating a new database named example_db.

CREATE DATABASE example_db;

In this example, we create a database named example_db.

Step 2: Creating a Table

In this step, we create a table named sales within the previously created database.

USE example_db;

CREATE TABLE sales (
    sale_id INT AUTO_INCREMENT PRIMARY KEY,
    product_id INT,
    sale_date DATE,
    quantity INT,
    price DECIMAL(10, 2)
);

Here, we define the sales table with columns for sale_id, product_id, sale_date, quantity, and price. The sale_id column is set as the primary key and will auto-increment.

Step 3: Inserting Data into the Table

This step involves inserting some sample data into the sales table.

INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (1, '2023-01-01', 2, 19.99);
INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (2, '2023-01-02', 1, 9.99);
INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (1, '2023-01-03', 3, 19.99);
INSERT INTO sales (product_id, sale_date, quantity, price) VALUES (3, '2023-01-04', 5, 29.99);

Here, we insert four rows of data into the sales table.

Step 4: Using the HAVING Clause

This step involves using the HAVING clause to filter the total sales for each product, only showing products with total sales greater than $30.

SELECT product_id, SUM(quantity * price) AS total_revenue
FROM sales
GROUP BY product_id
HAVING SUM(quantity * price) > 30;

This command groups the sales by product_id, calculates the total revenue for each product, and filters the results to only show products with total revenue greater than $30.