SQLite分组统计AAC与非AAC音轨时如何添加总计行并解决GROUP BY报错

问题原因 #

• 执行顺序冲突：SQL执行顺序中GROUP BY早于SELECT字段计算，你在SELECT的CASE判断中使用了只有聚合完成后才能得到的COUNT()结果，又将该CASE的别名作为GROUP BY的分组依据，逻辑矛盾，因此触发“GROUP BY子句中不允许使用聚合函数”的报错。
• 分类逻辑错误：第二个WHEN的判断条件m.name NOT LIKE '% AAC %' OR m.name NOT LIKE 'AAC %'永远为真，所有数据都会命中该分支，第三个WHEN的总计判断永远不会生效。
• 总计逻辑设计错误：总计行是全部分组的汇总结果，不属于单条数据的分类维度，不能直接写入单条数据的分类判断逻辑中。

修复方案 #

方案1：使用ROLLUP（SQLite 3.30.0及以上版本支持，写法简洁） #

SELECT 
  CASE
    WHEN GROUPING(m.name) = 1 THEN 'Total'
    WHEN m.name LIKE '% AAC %' OR m.name LIKE 'AAC %' THEN 'AAC'
    ELSE 'non-AAC'
  END AS Media,
  COUNT(t.MediaTypeID) AS Tracks
FROM MediaType m
INNER JOIN Track t ON m.MediaTypeID = t.MediaTypeID
GROUP BY ROLLUP(
  CASE
    WHEN m.name LIKE '% AAC %' OR m.name LIKE 'AAC %' THEN 'AAC'
    ELSE 'non-AAC'
  END
)

逻辑说明：ROLLUP会自动生成汇总行，GROUPING函数在汇总行上返回1，直接将对应分类命名为Total即可，分组维度仅使用无聚合的分类逻辑，符合SQL执行顺序要求。

方案2：使用UNION ALL（全版本兼容） #

-- 统计AAC、非AAC分类数量
SELECT 
  CASE
    WHEN m.name LIKE '% AAC %' OR m.name LIKE 'AAC %' THEN 'AAC'
    ELSE 'non-AAC'
  END AS Media,
  COUNT(t.MediaTypeID) AS Tracks
FROM MediaType m
INNER JOIN Track t ON m.MediaTypeID = t.MediaTypeID
GROUP BY 1

UNION ALL

-- 追加总计行
SELECT 'Total' AS Media, COUNT(t.MediaTypeID) AS Tracks
FROM MediaType m
INNER JOIN Track t ON m.MediaTypeID = t.MediaTypeID

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