Group by year, semiannual, four-month, quarterly, bimonthly and monthly MYSQL

Question

MySQL Version 8.0 Schema SQL

CREATE TABLE IF NOT EXISTS `printer` (
  `id` INT NOT NULL,
  `name` VARCHAR(45) NOT NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;


CREATE TABLE IF NOT EXISTS `print` (
  `id` INT NOT NULL,
  `pages` INT NOT NULL,
  `copies` INT NOT NULL,
  `date` DATE NOT NULL,
  `printer_id` INT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_print_printer_idx` (`printer_id` ASC) VISIBLE,
  CONSTRAINT `fk_print_printer`
    FOREIGN KEY (`printer_id`)
    REFERENCES `printer` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
SET SESSION sql_mode = '';
insert into printer (id,name)
values
(1, 'printer-1'),
(2, 'printer-2'),
(3, 'printer-3');

insert into print (id,pages,copies,date,printer_id)
values
(1, 3, 2, Date('2020-01-13'), 1),
(2, 2, 1, Date('2020-01-15'), 1),
(3, 5, 2, Date('2020-01-15'), 2),
(4, 2, 1, Date('2020-02-16'), 1),
(5, 6, 2, Date('2020-02-20'), 2),
(6, 1, 5, Date('2020-02-25'), 1),
(7, 4, 1, Date('2020-07-16'), 2),
(8, 1, 2, Date('2020-07-18'), 1),
(9, 2, 1, Date('2020-10-20'), 1),
(10, 2, 1, Date('2020-11-10'), 1),
(11, 2, 1, Date('2020-12-15'), 1),
(12, 3, 3, Date('2020-12-16'), 1),
(13, 2, 1, Date('2020-12-20'), 1),
(14, 5, 2, Date('2020-12-25'), 1),
(15, 2, 2, Date('2021-01-11'), 1),
(16, 6, 1, Date('2021-01-11'), 2),
(17, 2, 5, Date('2021-01-15'), 2),
(18, 2, 1, Date('2021-01-16'), 1),
(19, 5, 7, Date('2021-01-20'), 3),
(20, 2, 1, Date('2021-01-20'), 1);

Data from user (I'm using variables to simulate the data sent by the user):

SET @year_from = "2020";
SET @year_to = "2021";
SET @group = 6;

My Query:

SELECT YEAR(date), QUARTER(date), sum(copies*pages)
  FROM print
WHERE YEAR(date) >= @year_from AND YEAR(date) <= @year_to
GROUP BY YEAR(date), QUARTER(date)
ORDER BY YEAR(date), QUARTER(date)

Result:

YEAR(date)  QUARTER(date)   sum(copies*pages)
2020              1               37
2020              3                6
2020              4               27
2021              1               59

The problem:

1. I need this line too;

    YEAR(date)  QUARTER(date)   sum(copies*pages)
    2020              1               37
 -> 2020              2                0 <-
    2020              3                6
    2020              4               27
    2021              1               59

2. This query is not dynamic. I need the grouping for 1, 2, 3, 4, or 6 months to be dynamic, as this will be informed by the user.

As I am not an expert in sql, I ask your help with the query.

If someone can help me, follow the fiddle.

https://www.db-fiddle.com/f/gMXUGiAwVpyQemC2cvUFyf/0

I thank you all for your help.

ps.: If the construction of this query is not viable, I think that perhaps the solution is to extract the monthly report and, programmatically, make the groupings in the application. If that's the case, please let me know.

question from:https://stackoverflow.com/questions/65852265/group-by-year-semiannual-four-month-quarterly-bimonthly-and-monthly-mysql

Answer 1

To get values for each year/quarter combination, you can generate lists of all the years and quarters, and then LEFT JOIN them to your print table. For example, using recursive CTEs:

WITH RECURSIVE years AS (
  SELECT @year_from AS year
  UNION ALL
  SELECT year + 1 
  FROM years
  WHERE year < @year_to
),
quarters AS (
  SELECT 1 AS quarter
  UNION ALL
  SELECT quarter + 1 
  FROM quarters
  WHERE quarter < 4
)
SELECT y.year, q.quarter, COALESCE(sum(copies*pages), 0)
FROM quarters q
CROSS JOIN years y
LEFT JOIN print ON QUARTER(date) = q.quarter AND YEAR(date) = y.year
GROUP BY y.year, q.quarter
ORDER BY y.year, q.quarter

Output:

year    quarter     COALESCE(sum(copies*pages), 0)
2020    1           37
2020    2           0
2020    3           6
2020    4           27
2021    1           59
2021    2           0
2021    3           0
2021    4           0

Demo on db-fiddle

In terms of making the grouping variable (number of months controlled by the @group variable), we can change the quarters CTE to compute periods instead and change the JOIN condition to compute the period corresponding to the MONTH value of the date:

WITH RECURSIVE years AS (
  SELECT @year_from AS year
  UNION ALL
  SELECT year + 1 
  FROM years
  WHERE year < @year_to
),
periods AS (
  SELECT 1 AS period
  UNION ALL
  SELECT period + 1 
  FROM periods
  WHERE period < 12 / @group
)
SELECT y.year, p.period, COALESCE(sum(copies*pages), 0)
FROM years y 
CROSS JOIN periods p
LEFT JOIN print ON YEAR(date) = y.year AND FLOOR((MONTH(date)-1)/@group) + 1 = p.period
GROUP BY y.year, p.period
ORDER BY y.year, p.period

For @group = 6, this yields:

year    period  COALESCE(sum(copies*pages), 0)
2020    1       37
2020    2       33
2021    1       59
2021    2       0

Demo on dbfiddle