MariaDB Having Clause

The HAVING clause is crucial for filtering and aggregating data in database queries. The MariaDB HAVING clause syntax is outlined. It breaks down into components. These include the SELECT clause, aggregate functions, and the GROUP BY clause. The SELECT clause specifies columns. Aggregate functions perform calculations on data. The GROUP BY clause groups results by a specified column. The article emphasizes that the HAVING clause follows the GROUP BY clause. It is used to apply conditions involving aggregate features. We will explore the HAVING clause in MariaDB in this article. It will cover its syntax, use cases, and implementation.

MariaDB HAVING Clause

The HAVING clause is an extension of the WHERE clause in SQL queries. While the WHERE clause filters rows earlier than they may be grouped and aggregated, the HAVING clause filters the effects after grouping and aggregation. Conditions using aggregate functions are specified using the MariaDB HAVING clause. The GROUP BY clause comes earlier than the having clause.

Syntax:

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

• SELECT Clause: It specifies the columns you want to retrieve from the table like col1.
• Aggregate Function: It is applied to the values in col2. Multiple values are calculated via aggregate functions, which then produce a single result.. Examples of aggregate functions are SUM, COUNT, AVG, MIN, and MAX.
• FROM Clause: It specifies the table from which the data will be retrieved.
• GROUP BY Clause: It groups the result set by the specified column (col1).
• HAVING Clause: It is used to filter the results after they have been grouped. Aggregate function is applied on col2 within having clause

Example: MariaDB HAVING Clause

Let’s look at how to use the HAVING clause by creating an example table and inserting the data into it.
Create Table

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100),
    category VARCHAR(50),
    unit_price DECIMAL(10, 2),
    stock_quantity INT
);

Insert Data

INSERT INTO products VALUES
(1, 'Laptop', 'Electronics', 1200.00, 50),
(2, 'Smartphone', 'Electronics', 800.00, 100),
(3, 'Coffee Maker', 'Appliances', 50.00, 30),
(4, 'Backpack', 'Fashion', 40.00, 80),
(5, 'Desk Chair', 'Furniture', 150.00, 20);

Example 1 : Using SUM Function Find Categories with Total Stock Quantity Greater Than 100.

The SUM function calculates the the sum of all values in the specified column.

Syntax:

SELECT category, SUM(stock_quantity) AS total_stock FROM products GROUP BY category HAVING total_stock > 100;

This query selects the category and sum the stock_quantity from products tables. After that, it groups the result by category using having filter that total stock is greater than 100. The output will be the list of only those categories where the total stock is greater than 100.

Output:

HAVING Clause

Example 2: Using AVG Function Identify Categories with Average unit price above price 50.

The average function calculates the average value of the specified colum

Syntax:

SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING avg_price > 50;

This query selects the category and average the unit price for each category from the products table. After that, it groups the results by category using having filter that average price is greater than 50. The output of this query includes only thode categories whose average unit price is greater than 50.

Output:

HAVING Clause

Example 3: Using Count Function Get Categories with More than Three Products.

The count function counts the number of rows in the specified column or counts all rows in the table if no column is specified.

Syntax:

SELECT category, COUNT(product_id) AS product_count FROM products GROUP BY category HAVING product_count > 3;

This query selects the category and count product id and then group the result by category using having filter that product id is greater than 3. The output of this query is only those categories where the count of product id is greater than 3, along with the corresponding product count for each category.

Output:

HAVING Clause

Example 4: Using MAX Function Retrieve Products with Maximum Unit Price in Each Category.

The MAX function retrieves the largest value in the specified column.

Syntax:

SELECT category, MAX(unit_price) AS max_price FROM products GROUP BY category HAVING max_price > 50;

This query selects the category and maximum unit price and then group the result for each category using having filter that unit price should be greater than 50. The output of this query includes only those categories where the maximum unit price is greater than 50.

Output:

HAVING Clause

Example 5: Using SUM Function Find Categories with Total Unit Price Grater Than Total Price 500.

Syntax:

SELECT category, SUM(unit_price) AS total_price FROM products GROUP BY category HAVING total_price > 500;

This query selects the category and sum the unit price and then group the result by category using having filter that total price is greater than 500.

Output:

Example 6: Using MIN Function Find Categories With the Lowest Minimum Unit Price.

The MIN function retrieves the smallest value in the specified column.

Syntax:

SELECT category, MIN(unit_price) AS min_price FROM products GROUP BY category HAVING min_price = (SELECT MIN(unit_price) FROM products);

This query selects the category and minimum unit price for each category and then groups the result by category and filter using having clause to only include groups where the minimum unit price is equal to the overall minimum unit price.

Output:

HAVING Clause

Example 7: HAVING Clause with Multiple Conditions.

When using the HAVING clause with multiple conditions we can combine them using logical operators. The example of a query with the multiple conditions in HAVING clause is given below:

Syntax:

SELECT category, AVG(unit_price) AS avg_price FROM products GROUP BY category HAVING avg_price > 50 AND avg_price < 100;

This query selects the category and the average unit price for each category and then groups the results by category and then filters the groups using the HAVING clause that only those with the average unit price greater than 50 and less than 100. The output displays the category and the corresponding average unit price for categories that meet the specified conditions.

Output:

HAVING Clause

Conclusion

In end, the HAVING clause in MariaDB is a powerful tool. It filters aggregated data and allows developers to extract insights. This helps them find meaningful information in their databases. The article outlined the syntax and components of the HAVING clause, emphasizing its relationship with the SELECT, GROUP BY, and aggregate functions. The HAVING clause comes into play after the GROUP BY clause and is used to apply conditions involving aggregate features.