MariaDB GROUP BY Clause


Objectives

  • Understanding of MariaDB GROUP BY Clause

  • MariaDB GROUP BY Clause with COUNT Function

  • MariaDB GROUP BY Clause with AVG Function

  • MariaDB GROUP BY Clause with SUM Function

  • MariaDB GROUP BY Clause with MAX Function

  • MariaDB GROUP BY Clause with MIN Function


Create database and table and insert data if it is not created already

--- Create database if not exist
CREATE DATABASE IF NOT EXISTS mariadb_tutorial;

--- Select the database for further operation
USE mariadb_tutorial;

--- Create table if not exist
CREATE TABLE IF NOT EXISTS person (
    id int(12) NOT NULL AUTO_INCREMENT,
    first_name varchar(150) NOT NULL,
    last_name varchar(150),
    email varchar(100),
    age int,
    income double,
    PRIMARY KEY (id)
)

--- Remove old Data
TRUNCATE person;

--- Insert data into table if not exist
INSERT INTO person (first_name, last_name, email, age, income)
VALUES
	('Faiyaz', 'Mia', 'faiyaz@pf.local', 10, 5000),
	('John', 'Doe', 'john@pf.local', 19, 100),
	('Tahsin', NULL, 'tahsin@pf.local', 10, 150),
	('Jane', 'Doe', 'jane@gmail.com', 24, 300),
	('Rakib', 'Mia', 'rakib@bf.local', 24, 200),
	('Sagor', 'Sowrov', 'email10@bf.local', 20, 250),
	('Touhid', NULL, 'hmtmcse.com@gmail.com', 30, 500);


Understanding of MariaDB GROUP BY Clause

GROUP BY Clause groups the rows that have same values and summaries those values according to Aggregation function such as COUNT, SUM, AVG, MAX, MIN etc.


MariaDB GROUP BY Clause with COUNT Function

Problem : Find the total person by age

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name(s);

Example

SELECT age AS Age, COUNT(age) AS Total FROM person GROUP BY age;

Output

+------+-------+
| Age  | Total |
+------+-------+
|   10 |     2 |
|   19 |     1 |
|   20 |     1 |
|   24 |     2 |
|   30 |     1 |
+------+-------+
5 rows in set (0.000 sec)


MariaDB GROUP BY Clause with AVG Function

Problem : Find the average salary by age

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name(s);

Example

SELECT age AS Age, AVG(income) AS AvarageSalary FROM person GROUP BY age;

Output

+------+---------------+
| Age  | AvarageSalary |
+------+---------------+
|   10 |          2575 |
|   19 |           100 |
|   20 |           250 |
|   24 |           250 |
|   30 |           500 |
+------+---------------+
5 rows in set (0.096 sec)


MariaDB GROUP BY Clause with SUM Function

Problem : Find the total salary by age

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name(s);

Example

SELECT age AS Age, SUM(income) AS TotalSalary FROM person GROUP BY age;

Output

+------+-------------+
| Age  | TotalSalary |
+------+-------------+
|   10 |        5150 |
|   19 |         100 |
|   20 |         250 |
|   24 |         500 |
|   30 |         500 |
+------+-------------+
5 rows in set (0.000 sec)


MariaDB GROUP BY Clause with MAX Function

Problem : Find the maximum salary by age

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name(s);

Example

SELECT age AS Age, MAX(income) AS MaximumSalary FROM person GROUP BY age;

Output

+------+---------------+
| Age  | MaximumSalary |
+------+---------------+
|   10 |          5000 |
|   19 |           100 |
|   20 |           250 |
|   24 |           300 |
|   30 |           500 |
+------+---------------+
5 rows in set (0.000 sec)


MariaDB GROUP BY Clause with MIN Function

Problem : Find the minimum salary by age

Syntax

SELECT column_name(s) FROM table_name GROUP BY column_name(s);

Example

SELECT age AS Age, MIN(income) AS MinimumSalary FROM person GROUP BY age;

Output

+------+---------------+
| Age  | MinimumSalary |
+------+---------------+
|   10 |           150 |
|   19 |           100 |
|   20 |           250 |
|   24 |           200 |
|   30 |           500 |
+------+---------------+
5 rows in set (0.001 sec)


MariaDB GROUP BY Clause Video tutorial

Share