SQL Group By Month

Problem

Suppose we have a table containing employee information as shown below:

The goal is to order the data above based on the month of starting. This is described in the start_date column.

How can we approach such a problem?

Solution 1 – Group by Column (extract)

The first method we can use is the extract() function in SQL. It allows us to extract specific parts from a date object.

Hence, we can use the extract() function to extract the month the date and group the data from the resulting value.

An example query is as shown:

SELECT

MAX

(

START_DATE

)

MAX

(

START_DATE

)

FROM

EMPLOYEES

GROUP

BY

EXTRACT

(

MONTH

FROM

START_DATE

)

;

The above should group the data by the months and return the max date in each month group:

Solution 2 – Date format

You can also use the date_format function to group data by the month name. An example is as shown:

SELECT

DATE_FORMAT

(

‘month’

,

START_DATE

)

AS

START_MONTH

,

COUNT

(

ID

)

AS

ID

FROM

EMPLOYEES

GROUP

BY

DATE_FORMAT

(

‘month’

,

START_DATE

)

;

This should group the data based on the month names and apply the count function.

Thanks for reading!!