How to Group by Month in Oracle

Problem

You'd like to group records by month in an Oracle database.

Example

Our database has a table named lamps with data in the columns id, color, and production_timestamp.

Solution

You can use two EXTRACT() functions to group records in a table by month and year.

Here's the query you would write:

SELECT
  EXTRACT(YEAR FROM production_timestamp) AS year,
  EXTRACT(MONTH FROM  production_timestamp) AS month,
  COUNT(id) AS count
FROM lamps
GROUP BY 
  EXTRACT(YEAR FROM production_timestamp), 
  EXTRACT(MONTH FROM production_timestamp);

Here’s the result of the query:

Discussion

Grouping records by month is a very common operation in Oracle. In our example, the number of lamps produced is totaled for each month. You usually want to take into account both the year and the month, i.e. you want to group lamps produced in January 2022 separately from lamps produced in January 2021.

To group data by month in Oracle, use the EXTRACT() function. It extracts the given part (year, month, etc.) from the date or timestamp. We use the EXTRACT() function twice: once with the MONTH argument and once with the YEAR argument. These extract the month and year respectively from the production date. We combine the two calls to EXTRACT() in the GROUP BY clause to group lamps by their production month and year. We use the same two calls to EXTRACT() in the SELECT clause to display the label for the year and month.