SQL Count Group By

The GROUP BY clause in SQL allows you to group rows that match a specific condition into a single group—for example, all users from the same department.

We can use the group by clause in conjunction with the count function to determine the number of items within a specific group. This can be useful when you need to determine duplicate rows.

Syntax

We can use the group by clause in combination with the count function as shown in the syntax below:

SELECT column1 , COUNT(column2)
FROM TABLE_NAME
GROUP BY column1

Let us illustrate how we can use the group by clause with the count function.

Sample Data

Start by creating a table and adding sample data as shown in the example statements below:

CREATE TABLE employees (
id serial PRIMARY KEY,
full_name VARCHAR(255),
email VARCHAR(255),
department VARCHAR(100),
start_date DATE,
active bool,
category VARCHAR(50)
);
INSERT INTO employees (full_name, email, department, start_date, active, category)
VALUES
('Meghan Edwards', '[email protected]', 'Game Development', '2016-06-22', TRUE, 'DEV-G1'),
('Sam Davis', '[email protected]', 'Game Development', '2014-01-04', FALSE, 'DEV-G1'),
('Taylor Miller', '[email protected]', 'Game Development', '2019-10-06', TRUE, 'DEV-G1'),
('Wilson Smitty', '[email protected]', 'Database Developer', '2016-12-23', TRUE, 'DEV-DB1'),
('Barbara Moore', '[email protected]', 'Database Developer', '2015-12-23', TRUE, 'DEV-DB1'),
('Raj Chan', '[email protected]', 'Database Developer', '2017-12-23', FALSE, 'DEV-DB1'),
('Susan Brown', '[email protected]', 'DevOps Engineer', '2011-12-23', TRUE, 'DEV-OPS1'),
('Marial Anderson', '[email protected]', 'DevOps Engineer', '2015-12-23', TRUE, 'DEV-OPS1'),
('Nancy Thomas', '[email protected]', 'DevOps Engineer', '2014-12-23', FALSE, 'DEV-OPS1');

NOTE: The above queries are implemented on the PostgreSQL database. Feel free to modify the queries for your database engine.

We can select all the records from the table as shown:

SELECT * FROM employees;

The table contains records as shown:

Example Usage

We can now use the group by clause with the count function to determine the number of employees in various categories.

An example query is as shown:

SELECT DEPARTMENT,
CATEGORY,
COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT,
CATEGORY;

The code above should classify the data based on the department and category column. Then, the count function should count the number of records in each group as shown:

Example Usage 2

We can also use the group by function with an order by clause as shown:

SELECT DEPARTMENT,
CATEGORY,
COUNT(*)
FROM EMPLOYEES
GROUP BY DEPARTMENT,
CATEGORY
ORDER BY COUNT(*) ASC;

The code above should return:

Conclusion

This tutorial discusses how to use the group by clause in conjunction with the count function.

Thanks for reading!!