MariaDB GROUP BY Clause

An important feature in MariaDB, and SQL in general, is the GROUP BY clause. This article explains the important features of the GROUP BY clause in MariaDB. This article explains you the syntax of the GROUP BY clause and its practical applications. Examples include using the aggregate functions, grouping by single and multiple columns, and using GROUP BY with HAVING clause.

GROUP BY Clause

The GROUP BY clause in MariaDB is used to group rows with similar values in the specified columns into grouped rows, such as "total" or "count" It is often used with aggregation functions such as COUNT(), SUM(), AVG(), MIN(), or MAX() to perform calculations on any of a group of rows.

Syntax:

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

• 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. Aggregate functions perform calculations on multiple values and return a single value. 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).

Example: GROUP BY Clause

Let's look at how to use the GROUP BY 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 Aggregate Function SUM

Aggregate function SUM used to calculate the sum of values in a specified column. Lets see how to use the GROUP BY clause with the SUM function:

Syntax:

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

This query groups products by their categories and calculates the total stock quantity using the aggregate function SUM for each category. The output shows the total stock quantity for each category in the products table.

Output:

Example 2: Grouping by Multiple Columns

Grouping by multiple column means that the result set is organized into groups on unique combinations of values in more than one column. When we will use the GROUP BY clause with the multiple columns , the query groups the rows that have same values into a single result row.

SELECT category, unit_price, AVG(stock_quantity) AS avg_quantity FROM products GROUP BY category, unit_price;

This query groups products by their categories and calculates the average unit price using the aggregate function AVG for each category. The output of this query will be the three columns: category, unit_price and stock_quantity that contain the average stock quantity for each unique combination of category and unit_price in the products table.

Output:

Example 3: Grouping by a Single Column Number

Grouping by a single column means that you want to group the rows based on the distinct values in that particular column. The result contains the set of rows where each row represents a unique value in the specified column.

SELECT category, COUNT(*) AS product_count FROM products GROUP BY category;

This query counts the number of products in each category. The output shows the count of products for each unique category.

Output:

Example 4: Using Aggregate Function MAX

Aggregate function MAX used to calculate the maximum values in a specified column. Lets see how to use the GROUP BY clause with the MAX function:

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

This query identifies the products with the maximum unit price in each category. The out contains the maximum unit price for each category in the products table. In this example, for the Appliances the maximum unit price is 50, for Electronics it is 1200, for Fashion it is 40 and for Furniture it is 150.

Output:

Example 5: Using Aggregate Function SUM.

SELECT category, SUM(unit_price * stock_quantity) AS total_value FROM products GROUP BY category;

This query calculates the total value of stock for each category by multiplying unit price with stock quantity. The output shows the total value for each unique category in the products table.

Output:

Example 6: Total Stock Value for the Entire Inventory Using SUM Aggregate Function.

SELECT SUM(unit_price * stock_quantity) AS total_inventory_value FROM products;

This query calculates the total value of the entire inventory by summing the product of unit price and stock quantity for all products.

Output:

Example 7: Filtering Groups with HAVING Clause.

Having clause is used to filter the results based on a condition after grouping. Let's see an example to filter the groups using the HAVING clause with GROUP BY clause:

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

This query groups products by category and calculates the average unit price for each category, but it only includes categories where the average unit price is greater than 100. In this example, the Electronics has an average price 1000 and Furniture has 200.

Output:

Example 8: Grouping by Expression

Grouping by expression means the ability to group data based on the result of an expression or calculation, rather than directly on a column. This is often achieved using a combination of functions and operators within the GROUP BY clause. It is useful when you want to categorize your data into different groups based on some criteria before performing the aggregate functions.

SELECT 
    category, 
    CASE
        WHEN stock_quantity > 50 THEN 'High Stock'
        WHEN stock_quantity <= 50 AND stock_quantity > 0 THEN 'Low Stock'
        ELSE 'Out of Stock'
    END AS stock_status,
    COUNT(*) AS product_count
FROM products
GROUP BY category, stock_status;

This query groups products by category and a custom expression stock_status, categorizing products based on their stock quantity. It then counts the number of products in each category and stock status combination. The output shows the count of products for each unique combination of category and stock_status.

Output:

Conclusion

The GROUP BY clause in MariaDB is a fundamental feature. It is effective and helps to organize and evaluate data easily. In this guide, we had explored the syntax of the GROUP BY clause and also examined its practical applications through various examples. Developers can accurately perform aggregate calculations by grouping rows. They can obtain totals, counts, averages, and identify max/min values.