How can you count and group the last 12 months in SQL?-CodePudding

Example

interviewed table to query:

id	interviewed_on_date
1	2020-12-31
2	2021-01-25
3	2021-01-25
4	2021-02-13
5	2022-05-25
6	2022-05-26

Result I would like to obtain:

date	count
2020-12	1
2021-01	3
2021-02	4
2022-05	6

Thanks for you help!

CodePudding user response：

AS you didn't specify your rdms, i used MySQL for my approach, as every rdms may have another syntax for this or even misses WINDOW functions all together.

Second in your description are you talking about data from the last 12 months , but you haven't any in your data, that is why i added on query without WHERE'clause, onyl to demonstrate that it works

The idea first select the count of all YEAR/months and then sue the cumultatove SUM function of the window function.

CREATE TABLE interview (
  `id` INTEGER,
  `interviewed_on_date`date
);

INSERT INTO interview
  (`id`, `interviewed_on_date`)
VALUES
  ('1', '2020-12-31'),
  ('2', '2021-01-25'),
  ('3', '2021-01-25'),
  ('4', '2021-02-13'),
  ('5', '2022-05-25'),
  ('6', '2022-05-26');

WITH CTE AS (SELECT
 DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
 COunt(*) count_
 FROM interview
 GROUP BY 1)
 SELECT myyeamon,
 sum(count_) OVER (ORDER BY  myyeamon) count_
 FROM CTE

myyeamon | count_
:------- | -----:
2020-12  |      1
2021-01  |      3
2021-02  |      4
2022-05  |      6

WITH CTE AS (SELECT
 DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
 COunt(*) count_
 FROM interview
 WHERE `interviewed_on_date` BETWEEN curdate() - INTERVAL - 12 MONTH AND curdate()
 GROUP BY 1)
 SELECT myyeamon,
 sum(count_) OVER (ORDER BY  myyeamon) count_
 FROM CTE

myyeamon | count_
:------- | -----:

db<>fiddle here

This is, when you only want the year 2022

WITH CTE AS (SELECT
 DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
 COunt(*) count_
 FROM interview
 WHERE YEAR(`interviewed_on_date`) = 2022
 GROUP BY 1)
 SELECT myyeamon,
 sum(count_) OVER (ORDER BY  myyeamon) count_
 FROM CTE

myyeamon | count_
:------- | -----:
2022-05  |      2

db<>fiddle here

To get the complete number but only the one entry you need LIMIT

WITH CTE AS (SELECT
 DATE_FORMAT(interviewed_on_date, '%Y-%m') myyeamon,
 COunt(*) count_
 FROM interview
 GROUP BY 1)
 SELECT myyeamon,
 sum(count_) OVER (ORDER BY  myyeamon) count_
 FROM CTE
 ORDER BY myyeamon DESC
 LIMIT 1

myyeamon | count_
:------- | -----:
2022-05  |      6

db<>fiddle here

CodePudding user response：

SELECT YEAR(interviewed_on_date), MONTH(interviewed_on_date), -- PK of result table.
    FORMAT(MAX(interviewed_on_date), 'yyyy-MM'), -- Label column.
    COUNT(*) -- Value column.
FROM interviewed
GROUP BY YEAR(interviewed_on_date), MONTH(interviewed_on_date)

MAX is an easy way to choose any old representative from each group of rows -- every row in each group has the same year and date.