Как объединиться по годам и месяцам в MySQL

Я хотел бы измерить количество ID и ATTRIBUTE из исходной таблицы и представить данные, как показано в "Желаемом отчете" ниже. Я использую MySQL.

Вот код, который у меня есть. Благодарю! И также имейте в виду, мне нужно извлечь месяц из поля даты в мои данные, но не знаю, как это сделать. Спасибо.

Рекомендуемый ответ не работает!! Не знаю, почему, вот ошибка, которую я получаю:

Ответы

Ответ 1

Этот запрос будет считать все строки и будет также считать только строки, в которых Attribute не является нулевым, группировка по годам и месяцам в строках:

SELECT
  Year(`date`),
  Month(`date`),
  Count(*) As Total_Rows,
  Count(`Attribute`) As Rows_With_Attribute
FROM your_table
GROUP BY Year(`date`), Month(`date`)

(это потому, что Count (*) подсчитывает все строки, Count (Attibute) подсчитывает все строки, где Attribute не является нулевым)

Если вам нужна ваша таблица в PIVOT, вы можете использовать ее для подсчета только строк, где Attribute не равно null:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then `Attribute` end) As Jan,
  Count(case when month(`date`)=2 then `Attribute` end) As Feb,
  Count(case when month(`date`)=3 then `Attribute` end) As Mar,
  ...
FROM your_table
GROUP BY Year(`date`)

И это для подсчета всех строк:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then id end) As Jan,
  Count(case when month(`date`)=2 then id end) As Feb,
  Count(case when month(`date`)=3 then id end) As Mar,
  ...
FROM your_table
GROUP BY Year(`date`)

(или вместо подсчета id вы можете использовать Sum(Month( date )=1), как в ответе kander). Конечно, вы можете комбинировать оба запроса с этим:

SELECT
  Year(`date`),
  Count(case when month(`date`)=1 then id end) As Jan_Tot,
  Count(case when month(`date`)=1 then `Attribute` end) As Jan_Attr,
  Count(case when month(`date`)=2 then id end) As Feb_Tot,
  Count(case when month(`date`)=2 then `Attribute` end) As Feb_Attr,
  Count(case when month(`date`)=3 then id end) As Mar_Tot,
  Count(case when month(`date`)=3 then `Attribute` end) As Mar_Attr,
  ...
FROM your_table
GROUP BY Year(`date`)

Ответ 2

Я придумал следующее, но имею в виду, что может быть более эффективный способ сделать это...

select
year(`date`) as 'YEAR',
sum(month(`date`) = 1) as 'JAN',
sum(month(`date`) = 2) as 'FEB',
sum(month(`date`) = 3) as 'MAR',
    -- Repeat until december
FROM `source`
WHERE `ATTRIBUTE` = 'XYZ'
GROUP BY 1;