MariaDB中出现Invalid use of group function错误的原因排查求助

错误根源 #

报错ERROR 1111 (HY000): Invalid use of group function是因为子查询中非法嵌套聚合函数：

( select sum(count(type)) as sum from statistics ) as StatisticsTotals

count(type)已经是对全表的聚合统计结果（单个数值），外层再用sum()包裹属于无效用法——sum()需要作用于一组值，而非单个聚合后的数值。

另外原查询逻辑存在疏漏：主查询通过where branch like 'a'过滤了数据，但子查询统计的是全表总数，两者范围不一致，会导致统计结果不符合预期。

修正后的SQL #

-- create
CREATE TABLE statistics (
  location text,
  type text,
  branch text,
  datetime date
);

-- insert
INSERT INTO statistics VALUES ('foo','a', 'a', '2024-01-01');
INSERT INTO statistics VALUES ('foo','a', 'a','2024-02-01');
INSERT INTO statistics VALUES ('foo','a', 'a','2024-03-01');
INSERT INTO statistics VALUES ('foo','a', 'a','2024-04-01');
INSERT INTO statistics VALUES ('foo','a', 'a','2024-05-01');
INSERT INTO statistics VALUES ('bar','a', 'a','2024-01-01');
INSERT INTO statistics VALUES ('bar','a', 'a','2024-02-01');
INSERT INTO statistics VALUES ('bar','a', 'a','2024-03-01');
INSERT INTO statistics VALUES ('bar','a', 'a','2024-04-01');
INSERT INTO statistics VALUES ('bar','a', 'a','2024-05-01');

-- fetch 
select
  location,
  count(s.type) as 'LocationTotal',
  StatisticsTotals.sum,
  monthname(datetime) as 'Month'
from  
    statistics s cross join
  ( select count(type) as sum from statistics where branch like 'a' ) as StatisticsTotals
where 
    branch like 'a'
group by 
    location,
    StatisticsTotals.sum,
    monthname(datetime),
    month(datetime)
order by 
    month(datetime)

关键修正点 #

1. 将子查询中的sum(count(type))改为count(type)，直接统计符合branch like 'a'条件的总条数
2. 子查询中添加where branch like 'a'，保证统计范围与主查询一致
3. 分组字段补充month(datetime)，避免MariaDB因ONLY_FULL_GROUP_BY模式导致的潜在报错

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