SQL Group By Week

The group by clause in SQL allows you to aggregate a set of rows that have the same value into a single entity/group. Think of it as a way to summarize a set of rows with similar values into one partition.

For example, we can categorize all the videos from the same youtube channel into a single group. You will often find the group by clause used in conjunction with other aggregate functions such as max, sum, avg, etc.

Although the group by clause is self-explanatory, it can be not easy when you need to group data by the week.

SQL Group By Week

Suppose you have a table containing the information shown 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');

To group the above data based on the week, we can use the extract function in SQL. The extract() allows you to extract a specific part of a date.

The syntax is as shown:

EXTRACT(part FROM DATE);

Hence, to extract the week from a date, we can pass the part parameter as WEEK followed by the date literal.

Consider an example below:

SELECT EXTRACT(week FROM TIMESTAMP '2022-04-08 15:57:54.937148+03');

NOTE: The above example is illustrated using PostgreSQL. In some databases, you do not need to specify the from clause.

The query above should return:

EXTRACT
---------
14

Hence, the timestamp above is from week 14 of the year.

We can pass this value to the group by clause as shown:

SELECT
EXTRACT(week FROM start_date) AS week,
COUNT(id) AS id
FROM employees
GROUP BY EXTRACT(week FROM start_date);

This should return:

Closing

This guide showed you how to use the group by clause in SQL and the extract function to group records based on the week.

SQL Group By Week

SQL Group By Week

Closing

About the author

John Otieno