How to group by month in SQL

Problem

You have a database with a date field and you want to group all entries by the month of that date.

Sample Data

Let's consider a table called Orders with the following data:

MySQL Solution

SELECT MONTH(OrderDate) as Month, COUNT(*) as TotalOrders
FROM Orders
GROUP BY MONTH(OrderDate);

Explanation (MySQL)

In this MySQL query, the MONTH() function is used to extract the month from the OrderDate field. The GROUP BY clause then groups the result-set by the month. The COUNT(*) function is used to get the number of orders per month.

PostgreSQL Solution

SELECT EXTRACT(MONTH FROM OrderDate) as Month, COUNT(*) as TotalOrders
FROM Orders
GROUP BY EXTRACT(MONTH FROM OrderDate);

Explanation (PostgreSQL)

In the PostgreSQL query, the EXTRACT(MONTH FROM OrderDate) function is used which works similarly to the MONTH() function in MySQL. It extracts the month from the OrderDate field. The GROUP BY clause is used to group the result-set by the month. The COUNT(*) function is used to get the number of orders per month.