Orders per month (SQLite)

Count and add up orders by month with strftime.

Dates & time · Beginner · sqlite · Not yet reviewed

SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(*)                      AS orders,
       SUM(amount)                   AS amount
FROM orders
GROUP BY month
ORDER BY month;

How it works

strftime('%Y-%m', order_date) turns a date such as 2024-03-15 into 2024-03. Grouping by it gives one row per month, and the text sorts in calendar order.

This is SQLite syntax. The same idea in other databases: DATE_TRUNC('month', order_date) in PostgreSQL, DATE_FORMAT(order_date, '%Y-%m') in MySQL, FORMAT(order_date, 'yyyy-MM') or DATEFROMPARTS in SQL Server.

What your tables need

- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)

Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).

Small example

Input data

orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paid

Expected result

month | orders | amount
2024-01 | 2 | 300.0
2024-02 | 2 | 450.0
2024-03 | 4 | 620.0

Common mistakes and limitations

- Grouping by month number only (3) merges March of different years. Group by year and month together. - Sorting by month name (March, April...) gives alphabetical order, not calendar order. - Dates stored as text in another format (for example 15/03/2024) do not work with strftime until they are converted to 2024-03-15.

Tags: strftime, dates, GROUP BY, month