MariaDB GROUP BY [With 9 useful example]

In this MariaDB tutorial, we will understand how to use the MariaDB GROUP BY clause and also look at several examples related to it. There are lists of the topic that comes under discussion:

MariaDB Group By Clause
MariaDB Group By Month
MariaDB Group By Having
MariaDB Group By Date
MariaDB Group By Order By
MariaDB Group By Concat
MariaDB Group By Sum
MariaDB Group By Count
MariaDB Group By Week
MariaDB Group by Where

Table of Contents

MariaDB Group By Clause

We’ll go through the MariaDB GROUP BY clause in great detail, using syntax and an example to assist us to comprehend it.

The group by clause in MariaDB divides a result’s rows into categories. The aggregate functions count(), min(), max(), sum(), and avg() are frequently used with the group by function to obtain attributes of groups.

For example, the number of elements (count), the total of values (sum), the maximum element (max), the minimum element (min), and the average of elements (avg).

First, let’s have a look at the MACK_DATA table by the following query:

SELECT * FROM MACK_DATA;

The SELECT statement is used to retrieve all records from the MACK_DATA table from the above MariaDB query. The MACK_DATA table contains around 1000 records in the database.

MariaDB group by — MariaDB SELECT statement for MACK_DATA table

The syntax of the group by clause is demonstrated as follows:

SELECT expression, aggregate_function(expression) FROM TABLE_NAME
WHERE [conditions]
GROUP BY COLUMN_NAME;

The syntax explanation:

expression: The expressions that must be included in the GROUP BY clause since they are not contained within an aggregate function.
aggregate_function: It could be a function like the SUM, COUNT, MIN, MAX, or AVG.
table_name: The table from which we need to retrieve records. There should be at least one table in the FROM clause.
WHERE [condition]: The condition should be met for the records to be selected.

Let’s have an illustrated example of the GROUP BY clause in the query:

SELECT * FROM MACK_DATA
WHERE DATA_ID>=20 
GROUP BY PERSON_GENDER;

As we see in the above query, we have retrieved all records from the MACK_DATA table based on the WHERE condition. In the condition where the data_id column is greater than or equal to 20.

And it groups the rows on the bases of the PERSON_GENDER column by using the GROUP BY clause in the MACK_DATA table with the help of the SELECT statement.

So, the output returns 8 records from the MACK_DATA table with the help of the GROUP BY clause in the SELECT statement.

MariaDB group by example — MariaDB GROUP BY clause Example

Read: MariaDB COUNT Function

MariaDB Group By Month

We’ll go through the MariaDB MONTH function using the GROUP BY clause, which will be explained using syntax and an example.

The MONTH function in MariaDB returns the month part of a date value. Let’s see the syntax of the MONTH function in the MariaDB by the following query:

SYNTAX:

SELECT MONTH(EXPRESSION) FROM TABLE_NAME
GROUP BY COLUMN_NAME;

The syntax explanation:

EXPRESSION: The Date or DateTime value from which we need to extract the month.

NOTE
The MariaDB MONTH function returns month value from 1 to 12 based on the given date or datetime value.

Let’s see illustrated example of the MariaDB MONTH function with the GROUP BY clause from the following query given below:

EXAMPLE:

SELECT MONTH(JOINING_DATETIME) AS EMPLOYEE_JOINING_DATETIME FROM EMPLOYEE 
GROUP BY EMP_ID
LIMIT 5;

In the preceding query, we have used the MONTH function on the JOINING_DATETIME column. And return the value of the month on the new column by alias name as EMPLOYEE_JOINING_DATETIME column from the EMPLOYEE table.

It was done based on grouping the EMP_ID column by using the GROUP BY clause and putting the LIMIT clause as LIMIT 5 to get the top 5 records from the EMPLOYEE table.

MariaDB group by month example — MariaDB MONTH function with GROUP BY clause EXAMPLE

Read: MariaDB JSON Function

MariaDB Group By Having

In this section, we will understand the use of the HAVING clause with the GROUP BY clause in detail and it is explained with the help of syntax and an example.

The MariaDB HAVING clause is used in conjunction with the GROUP BY clause to limit the returned rows to those that meet the condition. The syntax of the HAVING clause is given below:

SELECT EXPRESSION,AGGREGATE_FUNCTION(EXPRESSION)
FROM TABLE_NAME
WHERE [CONDITION]
GROUP BY EXPRESSION
HAVING EXPRESSION;

The explanation of syntax:

EXPRESSION: The expressions that must be included in the GROUP BY clause since they are not contained within an aggregate function.
AGGREGATE_FUNCTION: It could be a function like the SUM, COUNT, MIN, MAX, or AVG.
table_name: The table from which we need to retrieve records. There should be at least one table in the FROM clause.
WHERE [condition]: The condition should be met for the records to be selected.
HAVING condition: This is an additional condition that is only applied to aggregated results to limit the groupings of returned rows. The result set will only include those groups whose condition evaluates to TRUE.

Let’s see the illustrated example of the HAVING clause with the GROUP BY clause by the following query:

EXAMPLE:

SELECT * FROM MACK_DATA WHERE DATA_ID>=20 
GROUP BY PERSON_GENDER
HAVING COUNT(*)>=50;

In the preceding query, we have retrieved all records from the MACK_DATA table based on the WHERE condition in which the data_id column contains a value greater than or equal to 20.
And we have grouped them on the PERSON_GENDER column by using the GROUP BY clause.
It also counted to 50 as greater than or equal to based on the COUNT function by using the HAVING clause.

MariaDB group by having example — MariaDB GROUP BY clause with HAVING clause

Read: MariaDB Order By Clause

MariaDB Group By Date

In this section, we will learn how to use the MariaDB GROUP BY clause with the DATE function and which is explained with the help of illustrated syntax and an example.

To make it more understandable, we have used the MAX() function in the sub-topic of the MariaDB group by date. The MariaDB MAX function is an aggregate function that returns a max value in the result set. Let’s see the syntax of using the MAX function in the GROUP BY clause with the DATE function by the following query:

SYNTAX:

SELECT MAX(EXPRESSION) FROM TABLE_NAME 
WHERE [CONDITION]
GROUP BY DATE(COLUMN_NAME);

Here’s the illustrated example of the GROUP BY clause with the DATE function by the following query:

EXAMPLE:

SELECT MAX(JOINING_DATETIME) FROM EMPLOYEE
GROUP BY DATE(JOINING_DATETIME)
LIMIT 5;

As we see in the preceding query, we have used the MAX function on the JOINING_DATETIME column to find the maximum joining_datetime from the EMPLOYEE table.
And we have grouped them based on the DATE function of the JOINING_DATETIME column by using the GROUP BY clause.
As to get the result, we have used the LIMIT clause as LIMIT 5 to get the top 5 records for the resultset based on the function and clause used in the query from the EMPLOYEE table.

MariaDB group by date example — MariaDB GROUP BY clause with DATE function EXAMPLE

Read: MariaDB Union Operator

MariaDB Group By Order By

In this section, we will learn about the ORDER BY clause with the GROUP BY clause in detail and which is explained with the help of illustrated syntax and an example.

The ORDER BY clause in MariaDB is used to arrange the resultset entries. The syntax of the ORDER BY clause is given below by the following query:

SYNTAX:

SELECT EXPRESSION FROM TABLE_NAME
GROUP BY COLUMN_NAME 
ORDER BY COLUMN_NAME [DESC | ASC];

The syntax explanation:

EXPRESSION: The column or calculation that we want to retrieve.
TABLE_NAME: The table from which we’d like to get data. In the FROM clause, there should be at least one table.
GROUP_BY: In a SELECT statement, the MariaDB GROUP BY clause is used to collect data from numerous records and group the results by one or more columns.
ASC: It sorts the results in the ascending order if there is no modifier provided.
DESC: It sorts the results in descending order by COLUMN_NAME.

NOTE
If the ORDER BY clause does not include the ASC or DESC modifiers, the COLUMN_NAME will be sorted in ascending order. This is the same as the ORDER BY ASC phrase. And normally, the programmers don’t use the ASC modifier to arrange the result in the ascending order.

Here, in the illustrated example of the GROUP BY clause with the ORDER BY clause by the following query:

SELECT * FROM MACK_DATA 
GROUP BY PERSON_GENDER
ORDER BY PERSON_FIRSTNAME
LIMIT 5;

In this preceding query, we have retrieved all records from the MACK_DATA table and grouped all rows from the PERSON_GENDER column by using the GROUP BY clause.
And arrange the PERSON_FIRSTNAME column in the ascending order as no modifier is put by using the ORDER BY clause.
As the result, the LIMIT clause as LIMT 5 will bring the top 5 records from the MACK_DATA table by using the SELECT statement.

MariaDB group by order by example — MariaDB GROUP BY clause with ORDER BY clause Example

Read: MariaDB Select Statement

MariaDB Group By Concat

We’ll learn how to use the MariaDB GROUP BY clause with the CONCAT function in this part, which will be described using syntax and an example.

In MariaDB, the CONCAT_WS function’s full form is Concatenate with Separator and it is a special form of CONCAT function. The first argument is the separator for the rest of the arguments. The separator is used to add between the strings to be concatenated.

And the separator can be a string for the rest of the arguments. If the separator is NULL the rest value is NULL and all other NULL values will be skipped. Before jumping to an example, first, let’s see the CONCAT_WS function syntax by the following query:

SYNTAX:

SELECT CONCAT_WS(SEPARATOR,FIRST_STRING,SECOND_STRING) FROM TABLE_NAME
GROUP BY COLUMN_NAME;

The syntax explanation:

SEPARATOR: The separator can be a string or comma. If the value of the separator in the function is NULL then the rest value of the strings are also NULL.
FIRST_STRING: The first string which going to concatenate with another string.
SECOND_STRING: The second string will do the same process of concatenation to the first_string.

Now, let’s have illustrated example of the CONCAT_WS function with the GROUP BY clause by the following query:

EXAMPLE:

SELECT CONCAT_WS('  ',FIRST_NAME,LAST_NAME) AS'FULL_NAME_OF_EMPLOYEE' FROM EMPLOYEE
GROUP BY EMP_ID;

As we see in the above query, we have used the CONCAT_WS function to concatenate the FIRST_NAME and LAST_NAME columns in one word.

And the SEPARATOR is used to provide the same space in the new column as FULL_NAME_OF_EMPLOYEE from the EMPLOYEE table.

It also grouped them based on the EMP_ID column for the CONCAT_WS function by using the SELECT statement from the EMPLOYEE table.

MariaDB group by concat example — MariaDB CONCAT function with GROUP BY clause EXAMPLE

Read: MariaDB Join with Examples

MariaDB Group By Sum

We’ll learn how to utilize the MariaDB SUM function with the GROUP BY clause in this part, which is described using syntax and an example.

In MariaDB, the SUM function is an aggregate function that will return the sum of numbers in the output from the query. Let’s see the syntax of the SUM function with the GROUP BY clause in MariaDB by the following query:

SYNTAX:

SELECT expression, SUM(aggregate_function) FROM TABLE_NAME
WHERE [condition]
GROUP BY COLUMN_NAME;

The syntax explanation:

We must use the GROUP BY clause in the query if we want to return columns that aren’t summarized in the SUM function.

Let’s see the illustrated example of the SUM function with the GROUP BY clause by the following query:

EXAMPLE:

SELECT SUM(PRICE) FROM CUSTOMER_LIST 
GROUP BY ID 
LIMIT 5;

As we see in the above query, we have calculated the sum of numbers on the PRICE column by using the SUM function from the CUSTOMER_LIST. And we have grouped them based upon the ID column by using the GROUP BY clause and get the resultset of the top 5 records in the output by using the LIMIT clause as the LIMIT 5 keyword in the query.

MariaDB group by sum example — MariaDB SUM function with GROUP BY clause

Read: MariaDB Window functions

MariaDB Group By Count

We’ll learn how to utilize the MariaDB COUNT function with the GROUP BY clause in this part, which will be described using syntax and an example.

In MariaDB, the COUNT function returns the count of an expression or the column of the table. Here’s the syntax of the COUNT function with the GROUP BY clause in the following query:

SYNTAX:

SELECT EXPRESSION,COUNT(EXPRESSION) FROM TABLE_NAME
WHERE [CONDITION]
GROUP BY COLUMN_NAME;

Explanation of the syntax in detail:

EXPRESSION: Expressions that are not wrapped within the COUNT function and must be included in the SQL statement’s GROUP BY clause.
COUNT(EXPRESSION): This is the column or expression that will be tallied for non-null values.
TABLE_NAME: The name of the table from which we retrieve data. There should be at least one table in the FROM clause.
WHERE [condition]: It is a condition that should be met for the records to be selected from the WHERE condition.

Let’s see the sample example of the COUNT function with the GROUP BY clause:

EXAMPLE:

SELECT EMP_ID,COUNT(*) AS 'total_noof_male' FROM EMPLOYEE
WHERE EMP_GENDER='MALE'
GROUP BY EMP_GENDER;

Because the EMP_ID column is not wrapped in the COUNT function, we must use a GROUP BY clause in this COUNT function example. As a result, the EMP_ID column must be specified in the GROUP BY section of the MariaDB query.

MariaDB group by count example — MariaDB COUNT function with GROUP BY clause

Read: MariaDB Timestamp + Examples

MariaDB Group By Week

We will learn how to understand the MariaDB WEEK function with the GROUP BY clause in this section, which is described using syntax and an example.

The WEEK function in MariaDB retrieves the week component of a date value. Here is the syntax of the WEEK function given below by the following query:

SYNTAX:

SELECT WEEK(DATE_VALUE,MODE) FROM TABLE_NAME
WHERE [CONDITION]
GROUP BY COLUMN_NAME;

The syntax explanation:

DATE_VALUE: the date or date_Value from which we need to extract the week.
MODE: It is optional and it provides the date from which the date of the week starts. It can be one of the following:

MODE	EXPLANATION
0.	SUNDAY is the first day of the week, with a number ranging from 0 to 53.
1.	MONDAY is the first day of the week, and it has more than three days. It also returns the week value, which ranges from 0 to 53.
2.	The first day of the week is SUNDAY, which returns a week value ranging from 1 to 53.
3.	MONDAY is the first day of the week, and it has more than three days. It also returns the week value which ranges from 1 to 53.
4.	SUNDAY is the first day of the week, and it has more than three days. It also returns the week value which ranges from 0 to 53.
5.	The first day of the week is MONDAY, which returns a week value ranging from 0 to 53.
6.	SUNDAY is the first day of the week, and it has more than three days. It also returns the week value which ranges from 1 to 53.
7.	The first day of the week is MONDAY, which returns a week value ranging from 1 to 53.

MODE description and USE

NOTE:

Depending on the MODE supplied, the WEEK function will return a value ranging from 0 to 53 or 1 to 53.
The WEEKOFYEAR function will work the same as the WEEK function and the return value defined on the WEEK.

Now, let’s look at the WEEK function in MariaDB and how it is used on the MariaDB by the following query:

EXAMPLE:

SELECT WEEK(JOINING_DATETIME,0) FROM EMPLOYEE
GROUP BY EMP_ID;

The MariaDB WEEK function will return the week value on the basis of the MODE which is the first day of the week is SUNDAY.
So on the basis of that, the JOINING_DATETIME column will return the week number from the EMPLOYEE table by using the SELECT statement.
In the end of the query, we have used LIMIT clause as LIMIT 5 to return the top 5 records from the EMPLOYEE table.

MariaDB group by week example — MariaDB WEEK function with GROUP BY clause EXAMPLE

Read: MariaDB Row_Number Tutorial

MariaDB Group by Where

We’ll learn how to utilize the MariaDB WHERE clause with the GROUP BY clause in this part, which will be described using syntax and an example.

The WHERE clause is used in MariaDB to refine results from the INSERT, SELECT, UPDATE and DELETE queries. It is a condition that should be met for the records to be selected from the WHERE condition. The syntax of using the WHERE clause in the GROUP BY clause is given below by the following query:

SYNTAX:

SELECT expression FROM TABLE_NAME
WHERE [CONDITION]
GROUP BY COLUMN_NAME;

The syntax explanation:

WHERE [condition]: It is a condition that should be met for the records to be selected.

Let’s see a quick example of using the WHERE clause in the GROUP BY clause by the following query:

EXAMPLE:

SELECT * FROM EMPLOYEE WHERE EMP_GENDER='MALE'
GROUP BY JOINING_DATETIME;

As we see in the above query, we have used the WHERE condition to meet the records of the EMP_GENDER column as MALE from the EMPLOYEE table.
And we are also grouping them based on the JOINING_DATETIME column by using the GROUP BY clause in the SELECT statement.
As the output, with the help of the WHERE condition, it brought 15 records of the MALE from the EMP_GENDER column.

MariaDB group by where example — MariaDB GROUP BY clause with the WHERE clause EXAMPLE

You may also like to read the following MariaDB tutorials.

In this MariaDB tutorial, we understood the use of the MariaDB GROUP BY clause and also look at some samples. There are lists of the topic that comes under discussion:

MariaDB Group By Clause
MariaDB Group By Month
MariaDB Group By Having
MariaDB Group By Date
MariaDB Group By Order By
MariaDB Group By Concat
MariaDB Group By Sum
MariaDB Group By Count
MariaDB Group By Week
MariaDB Group by Where

Bijay

I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.

Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.