SQL GROUP BY Clause

In this lesson, you will learn how to group rows that have common values using SQL.

The GROUP BY Clause In SQL

The GROUP BY clause can be used in conjunction with the aggregate functions to group the returned rows that have common values and then perform calculations based on those groups.

For example, to discover how many users you have in each country you can group the result based on the country column, then with the COUNT() function you can display the number of users in each country.

Syntax

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s); 

Preparing A Database

Since you will learn how to group the retrieved rows, you must have a database table with some records having common data to practice grouping them. Execute the following query to create the database.

-- If there is already a database named "demo" delete it
DROP DATABASE IF EXISTS demo;

-- Create a new database called "demo"
CREATE DATABASE demo;

-- Select the "demo" database
USE demo;

-- Create a new table called "employees" inside the selected database
CREATE TABLE employees (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    gender VARCHAR(20),
    country VARCHAR(100),
    salary DECIMAL(7,2)
);

-- Insert 10 records in the "employees" table
-- We add null in the id column because its value will be generated automatically by the database server
INSERT INTO employees VALUES (null, "Emily", "female", "Denmark", 800);
INSERT INTO employees VALUES (null, "Stacy", "female", "Australia", 900);
INSERT INTO employees VALUES (null, "John", "male", "Canada", 750);
INSERT INTO employees VALUES (null, "Lana", "female", "Germany", 1000);
INSERT INTO employees VALUES (null, "Ronaldo", "male", "Brazil", 845);
INSERT INTO employees VALUES (null, "Louise", "female", "France", 700);
INSERT INTO employees VALUES (null, "James", "male", "Canada", 920);
INSERT INTO employees VALUES (null, "Maria", "female", "Brazil", 850);
INSERT INTO employees VALUES (null, "Mia", "female", "Canada", 780);
INSERT INTO employees VALUES (null, "Luke", "male", "Japan", 880);
    

This is the employees table that we created in the demo database.

Group By Single Column

The following query groups all employees based on their country and returns the country. So It will returns unique values.

Note: It's better to use the DISTINCT keyword if your purpose is just to return unique values.

SELECT country
FROM employees
GROUP BY country;
    

SELECT DISTINCT country
FROM employees;
    

The following query returns the number of employees in each country.
We group the rows that have the same country, and then we count the number of rows in each group.

SELECT country AS 'Country', COUNT(*) AS 'Total Employees'
FROM employees
GROUP BY country;
    

The following query returns the number of employees in each country in descending order.
We group the rows that have the same country, and then we count the number of rows in each group, and then we sort the result in descending order.

SELECT country AS 'Country', COUNT(*) AS 'Total_Employees'
FROM employees
GROUP BY country
ORDER BY Total_Employees DESC;
    

The following query returns the number of male employees in each country in descending order.
We group the rows that have the same country, and then we keep the rows where the gender is male, and then we count the number of rows in each group, and then we sort the result in descending order.

SELECT country AS 'Country', COUNT(*) AS 'Total_Male_Employees'
FROM employees
WHERE gender = 'male'
GROUP BY country
ORDER BY Total_Male_Employees DESC;
    

The following query returns the number of male and female employees.
We group the rows that have the same gender, and then we count the number of rows in each group.

SELECT gender AS 'Gender', COUNT(*) AS 'Total Employees'
FROM employees
GROUP BY country;
    

The following query returns the average salary in each country.
We group the rows that have the same country, and then we calculate the average salary in each group.

SELECT country AS 'Country', AVG(salary) AS 'Average Salaries'
FROM employees
GROUP BY country;
    

The following query returns the number of employees and the total salaries in each country.
We group the rows that have the same country, and then we count the number and the sum of salaries in each group.

SELECT country AS 'Country', COUNT(id) AS 'Total Employees', SUM(salary) AS 'Total Salaries'
FROM employees
GROUP BY country;
    

Group By Multiple Column

The following query returns the number of male and female employees in each country.
First, we group the rows that have the same country, and then we group them again based on their genders, and then we count the number of rows in each gender. At the end, we sort the results based on country names.

SELECT country AS 'Country', gender AS 'Gender', COUNT(id) AS 'Total Employees'
FROM employees
GROUP BY country, gender
ORDER BY country;
    

Group By Exercise

1- Write a query that creates a new database called "training".
Note: you should recreate it if it already exists.

2- Write a query that creates a table called "users" and check whether it exists or not. the table should contain the following fields:

• id INT NOT NULL PRIMARY KEY AUTO_INCREMENT
• username VARCHAR(50)
• role VARCHAR(20)
• join_date DATE

3- Write a query that inserts 10 rows in the "users" table.
Note: roles should be "admin", "editor", "writer" or "subscriber".

4- Write a query that returns how many users are in each role in the "users" table in ascending order.

DROP DATABASE IF EXISTS training;

CREATE DATABASE training;

USE training;

CREATE TABLE IF NOT EXISTS users (
	id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    role VARCHAR(20),
    join_date DATE
);

INSERT INTO users (username, role, join_date) VALUES
('Masha', 'admin', '2020-02-01'),
('Adam', 'editor', '2020-02-01'),
('Moly', 'subscriber', '2020-02-03'),
('Sandra', 'writer', '2020-02-04'),
('Lili', 'subscriber', '2020-02-04'),
('Moses', 'subscriber', '2020-02-04'),
('Jack', 'subscriber', '2020-02-07'),
('Ali', 'editor', '2020-02-10'),
('Alfred', 'writer', '2020-02-13'),
('Mohamed', 'writer', '2020-02-15');

SELECT role As 'Roles', COUNT(*) AS 'Total_Users'
FROM users
GROUP BY role
ORDER BY Total_Users;