Как сгруппировать данные по месяцам текущего года?

Question

[AlexSer]

Как сгруппировать данные по месяцам текущего года?

spoiler

Нужен запрос, который бы делал подсчет количества тестов по месяцам текущего года.
Т.е. месяцы в столбцы, test.title в строки и их количество в ячейках.

Comments

[AlexSer]

Короче пока искал, сам написал и вроде все работает))
Вот если у кого будет такая же проблема:

SELECT
  test.title,
  COUNT(CASE WHEN MONTH(`date_close`) = 1 THEN `id_test` END) AS Январь,
  COUNT(CASE WHEN MONTH(`date_close`) = 2 THEN `id_test` END) AS Февраль,
  COUNT(CASE WHEN MONTH(`date_close`) = 3 THEN `id_test` END) AS Март,
  COUNT(CASE WHEN MONTH(`date_close`) = 4 THEN `id_test` END) AS Апрель,
  COUNT(CASE WHEN MONTH(`date_close`) = 5 THEN `id_test` END) AS Май,
  COUNT(CASE WHEN MONTH(`date_close`) = 6 THEN `id_test` END) AS Июнь,
  COUNT(CASE WHEN MONTH(`date_close`) = 7 THEN `id_test` END) AS Июль,
  COUNT(CASE WHEN MONTH(`date_close`) = 8 THEN `id_test` END) AS Август,
  COUNT(CASE WHEN MONTH(`date_close`) = 9 THEN `id_test` END) AS Сентябрь,
  COUNT(CASE WHEN MONTH(`date_close`) = 10 THEN `id_test` END) AS Октябрь,
  COUNT(CASE WHEN MONTH(`date_close`) = 11 THEN `id_test` END) AS Ноябрь,
  COUNT(CASE WHEN MONTH(`date_close`) = 12 THEN `id_test` END) AS Декабрь
FROM
  test_selected
  LEFT JOIN test ON test_selected.id_test=test.id
  WHERE date_close BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY
  test.title

Теперь вопрос остался,можно ли "Итого" по строкам посчитать?

[alexalexes]

Можно

GROUP BY test.title WITH ROLLUP

[AlexSer]

alexalexes, хотел кое - что еще спросить!
Вот нашел инструкцию по динамическому транспонированию таблицы: Ссылка
А как посмотреть какая строка записывается в параметр @pivot_sql(какой запрос)?
Просто вроде сделал все как описано, их же таблицей и на конечном скрипте у меня выходит пустота.

[AlexSer]

Вот я выполняю запрос, для того, чтобы понять какой запрос пишется в @pivot_sql

SET @pivot_sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
    )
  )
INTO @pivot_sql
FROM
  bin_quantity;
  SELECT @pivot_sql;

Результат :
И я не могу посмотреть весь запрос))

[alexalexes]

select @pivot_sql;
Вообще, вы пытаетесь встать на путь извращения, заставляя СУБД вывести данные в том графическом виде, в котором их удобно воспринимать аля-Excel. Динамический запрос можно создать, но процедуру, которая его будет осуществлять, будет сложно сопровождать дальше, если потом делать изменения в отчете.
Для такой задачи с точки зрения SQL типичный подход такой, что вы пишите запрос, который возвращает выборку, в которой содержится исчерпывающий объем сведений, но представление выборки еще нужно дошлифовать.
Например, в вашем случае, вы бы написали запрос:

select t.title, MONTH(ts.date_close) as test_month, YEAR(ts.date_close) as test_year, count(ts.*) as test_count
FROM
  test_selected as ts
  LEFT JOIN test as t ON ts.id_test = t.id
  WHERE ts.date_close BETWEEN :date_begin AND :date_end
GROUP BY t.title, MONTH(ts.date_close),YEAR(ts.date_close)
ORDER BY test_year, test_month

из которого получите вполне плоскую выборку без динамических колонок, в которой будет название теста, месяц, год и количество тестов.
Далее, выходной отчет в удобочитаемом виде будет формировать приложение, которым и осуществлялось подключение к СУБД. Обычно, СУБД MySQL использует веб-сервер PHP или Python, но может быть какая-нибудь и другая среда. В этих средах скрипт содержит и выполняет как сам этот SQL запрос, так и алгоритм сборки выходного документа - html-страница, xls-файл, pdf-файл.
В таком виде разработчику удобно сопровождать и тестировать отдельно SQL запрос и отдельно сборку выходного документа.

[AlexSer]

Вот их запрос на PIVOT Таблицы

SET @pivot_sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT(
      'SUM(CASE WHEN collector = "', `collector`, '" THEN quantity ELSE 0 end) AS "', `collector`, '"'
    )
  )
INTO @pivot_sql
FROM
  bin_quantity;
SET @pivot_sql = CONCAT('SELECT bin, ', @pivot_sql, ' FROM bin_quantity GROUP BY bin');
PREPARE stmt FROM @pivot_sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Вот что записывается в @pivot_sql;

SELECT bin, SUM(CASE WHEN collector = "Person A" THEN quantity ELSE 0 end) AS "Person A",
SUM(CASE WHEN collector = "Person B" THEN quantity ELSE 0 end) AS "Person B",
SUM(CASE WHEN collector = "Person C" THEN quantity ELSE 0 end) AS "Person C"
 FROM bin_quantity GROUP BY bin

Когда выполняю просто этот запрос их таблица срабатывает, а вот полный запрос делаешь и передает 0 в чем проблема то?

[AlexSer]

alexalexes, Спс! Действительно пытаться сделать как в excell нет смысла, просто мне нужно для изучения.
Можете объяснить почему не срабатывает Pivot.Чисто для интереса. Я уже голову сломал))

Answer 1

[Александр]

SELECT COUNT(t1.*)
FROM test_selected t1
LEFT JOIN test t2 ON t1.id_test=t2.id
GROUP BY YEAR(t1.close_date)

Comments

[Александр]

SELECT COUNT(t1.*), t2.title
FROM test_selected t1
LEFT JOIN test t2 ON t1.id_test=t2.id
GROUP BY YEAR(t1.close_date), t2.title

[AlexSer]

Александр,
хотел кое - что еще спросить!
Вот нашел инструкцию по динамическому транспонированию таблицы:
ссылка
А как посмотреть какая строка записывается в параметр @pivot_sql?
Просто вроде сделал все как описано, их же таблицей и на конечном скрипте у меня выходит пустота.