sql - 按月和年分组

Question

我正在尝试计算按月和年分组的实例。

SELECT 
    (MONTH(CAST(t1.some_date as DATE)) + ' ' + YEAR(CAST(t1.some_date as DATE))) as Issue_Month,
    COUNT(*) as c
FROM
    (sub query) t1
GROUP BY 
    (MONTH(CAST(t1.some_date as DATE)) + ' ' + YEAR(CAST(t1.some_date as DATE)))

Error line 2: '+' cannot be applied to bigint, varchar(1)

我尝试将 + 换成 & & || 来构建所需的字符串，但我没有运气。

---

如何生成如下表:

Month Year  |  c

---

我如何生成这张表:

Month  |  Year  |  c

Answer 1

您可以使用 date_format() :

SELECT 
    date_format(CAST(t1.some_date as DATE), '%m %Y') as Issue_Month,
    COUNT(*) as c
FROM
    (sub query) t1
GROUP BY 
    date_format(CAST(t1.some_date as DATE), '%m %Y')