MariaDB - GROUP BY Clause

We can use the GROUP BY Clause in the SELECT Statement to assemble the rows with the same data in one or more columns. We can also use the GROUP BY clause by calculating the value using expressions using functions and operators except for the grouping function.

Syntax:

Select column_list from table_name [WHERE_condition] GROUP BY col_1, col_2;

• Column_list is the list of columns you want to retrieve.
• WHERE_condition is optional. We can apply a condition for the retrieval of rows.
• And col_1 mentioned after the GROUP BY clause is the column using which we have to group the result set.
• GROUP BY clause is usually used with the aggregate functions like count(), max(), sum(), min(), and avg() to calculate the properties of groups like a number of elements(count), the sum of the value of the column, the maximum value of the column(max), the minimum value of the value(min).

Syntax with Aggregate Function:

Select col_1, aggregate_function(col_2) from table_name GROUP BY col_1;

•           Aggregate_function is the function provided by MariaDB.

Let's see an example:

We will be using the table named 'Country'. Which consists of three columns as follows:

MariaDB [student]> create table country (state varchar(30),
 city varchar(30), 
Population int);

It looks like:

MariaDB [student]> select * from country;

Let's see an example for the group by :

In the following query, we will be using group by clause and segregating the data based on one column.

MariaDB [student]> select state, city from country group by state;

In this example, we have divided the rows based on the state. Hence we can see from every state the first occurring city is returned.

Using GROUP BY clause with the count() function:

In the following query, we use group by clause with count() to retrieve the total number of cities in each state.

MariaDB [student]>  select city,state, COUNT(city) FROM country GROUP BY state;

• Initially, the GROUP BY clause segregates the cities based on the states they are in.
• Then, the count() is applied to each state, and it returns the total number of states present in it. Here we can see four different cities in the table, and a total number of states are represented in the sttes_present column./

Using GROUP BY clause with the sum() function:

In the following query, we are using group by clause with sum() to calculate the total population present in each state.

MariaDB [student]>select state, sum(population) Total_Population from country group by state order by Total_population;

In the above example

• Initially, we are adding the population of each city present in a particular state.
• Then we are segregating the cities based on the state they belong to.
• Finally, we are also sorting them in ascending order using the ORDER BY clause.

Using GROUP BY clause with the min() and max() functions:

In the following Statement, we will be using min() and max() functions to find the minimum and maximum population of the cities in each state.

MariaDB [student]> select state,min(population) lowest_population, max(population) highest_population from country group by state order by state;

In the above example,

• Initially, the cities are grouped based on the states they belong to.
• Next, we have calculated the minimum value of population using the min() function and the maximum value of the population using the max() function from all the cities present in the particular state.
• Here we can see the lowest_population column and highest_population column, representing the populations' number in each city.
• Finally, we have sorted them based on state alphabetically in ascending order using order by clause.

Using GROUP BY clause with the avg() function:

In the following query, we will be using group by clause with avg() function t/o calculate the average population in each state.

MariaDB [student]> select state, avg(population) avg_population from country group by state order by avg_population desc;

In the above example, we can see the average population of each state is returned in the result set.

The cities are grouped based on state, and their average population is calculated and returned.

We have also ordered them in descending order using the DESC keyword with ORDER BY Clause.