Oracle及SQL Server按年月分组查询报错问题求助

问题描述 #

在Oracle SQL Developer中执行按「年月」分组的查询时，出现报错：

Not a group by expression

原执行SQL：

select 
    dcst.stateid as stateId,
    dcst.crtdt ,
    to_char(to_date(DCST.CRTDT,'DD-MON-YY HH.MI.SS."000000000" AM'),'Month') AS Month,
    to_date(DCST.CRTDT,'DD-MON-YY HH.MI.SS."000000000" AM') as datetimes
from
    TBL dcst
group by to_char(to_date(DCST.CRTDT,'MON-YY HH.MI.SS."000000000" AM'),'MON-YY')

去除GROUP BY后的数据示例：

Stateid   CRTDT                            MONTH     DATETIMES
506       22-DEC-22 06.08.17.480000000 PM  December  22-DEC-22
506       23-DEC-22 10.11.00.795000000 AM  December  23-DEC-22
853       10-JAN-23 12.30.45.212000000 AM  January   04-JAN-23

后续尝试中出现的其他报错：

• 添加stateId并按月份统计时，报错：not a single-group group function
• 简化查询后报错：Group function is not allowed here，对应SQL：

select 
    dcst.stateid as stateId,
    dcst.crtdt 
from tbl dcst
group by count(dcst.stateid), to_char(to_date(DCST.CRTDT,'MON-YY HH.MI.SS."000000000" AM'),'MON-YY')

预期输出：

12-22
2
01-23
1

问题原因与解决方法 #

核心问题分析 #

Oracle的GROUP BY规则有两个核心要求：

1. SELECT子句中的非聚合列必须全部出现在GROUP BY子句中
2. 聚合函数（如COUNT()）不能放在GROUP BY里，只能在SELECT或HAVING子句中使用

同时原SQL存在两处日期转换逻辑问题：

• SELECT与GROUP BY中的日期转换格式不匹配（SELECT用DD-MON-YY，GROUP BY用MON-YY），导致分组逻辑混乱
• 如果CRTDT本身是DATE/TIMESTAMP类型，无需用TO_DATE()重复转换，直接用TO_CHAR()格式化即可

正确SQL写法（Oracle版本） #

根据预期输出的年月+统计数需求，正确语句如下：

-- 若CRTDT为DATE/TIMESTAMP类型
SELECT
    TO_CHAR(dcst.crtdt, 'MM-YY') AS year_month,
    COUNT(dcst.stateid) AS count
FROM TBL dcst
GROUP BY TO_CHAR(dcst.crtdt, 'MM-YY')
ORDER BY year_month;

如果CRTDT是字符串类型，调整转换逻辑：

SELECT
    TO_CHAR(TO_DATE(dcst.crtdt, 'DD-MON-RR HH.MI.SS.FF9 AM'), 'MM-YY') AS year_month,
    COUNT(dcst.stateid) AS count
FROM TBL dcst
GROUP BY TO_CHAR(TO_DATE(dcst.crtdt, 'DD-MON-RR HH.MI.SS.FF9 AM'), 'MM-YY')
ORDER BY year_month;

SQL Server适配版本 #

SQL Server中日期格式化使用FORMAT()或CONVERT()，对应语句：

-- 若CRTDT为日期类型
SELECT
    FORMAT(dcst.crtdt, 'MM-yy') AS year_month,
    COUNT(dcst.stateid) AS count
FROM TBL dcst
GROUP BY FORMAT(dcst.crtdt, 'MM-yy')
ORDER BY year_month;

如果CRTDT是字符串类型：

SELECT
    FORMAT(CONVERT(DATETIME, dcst.crtdt, 109), 'MM-yy') AS year_month,
    COUNT(dcst.stateid) AS count
FROM TBL dcst
GROUP BY FORMAT(CONVERT(DATETIME, dcst.crtdt, 109), 'MM-yy')
ORDER BY year_month;

关键注意事项 #

• 聚合函数不能出现在GROUP BY子句中，仅可用于SELECT或HAVING过滤
• GROUP BY的字段必须与SELECT中除聚合函数外的字段完全匹配，或使用相同计算逻辑
• 日期类型字段避免重复转换，直接格式化效率更高
• 跨数据库兼容时，注意日期函数差异（Oracle用TO_CHAR，SQL Server用FORMAT/CONVERT）

内容的提问来源于stack exchange，提问作者Michele