如何让SQL仅显示COUNT(SKU)>10的结果并汇总已求和列？

需求一：仅显示COUNT(SKU) > 10的结果 #

你之前在WHERE子句里加COUNT(SKU) > 10没用，核心原因是WHERE用于分组前筛选原始行，聚合函数（比如COUNT）是分组后才计算的，这类聚合条件必须用HAVING子句，放在GROUP BY之后执行。

修改后的完整SQL如下：

select 
  loc_code, ship_lab, size_code, sku, COUNT(SKU) as sku_count
from sherwin.prdinv
where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') 
  and (loc_code like 'H0%' or loc_code like 'B%')
  and REGEXP_LIKE(SUBSTR(loc_code,3), '^[[:digit:]]+$')
group by loc_code, ship_lab, SKU, size_code
HAVING COUNT(SKU) > 10 -- 在此处添加聚合过滤条件
ORDER BY Loc_code asc;

需求二：对SUM函数求和的列添加总计汇总 #

假设你的查询包含类似SUM(字段名)的求和列（以下以SUM(stock_qty)作为示例求和列），提供两种常用实现方式：

方式1：用ROLLUP自动生成总计（推荐，支持ANSI SQL的数据库通用） #

ROLLUP会在分组结果基础上自动生成小计和总计行，配合GROUPING函数可标记总计行：

select 
  case when GROUPING(loc_code) = 1 then '总计' else loc_code end as loc_code,
  case when GROUPING(ship_lab) = 1 then '' else ship_lab end as ship_lab,
  case when GROUPING(size_code) = 1 then '' else size_code end as size_code,
  case when GROUPING(sku) = 1 then '' else sku end as sku,
  COUNT(SKU) as sku_count,
  SUM(stock_qty) as total_stock -- 示例求和列
from sherwin.prdinv
where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') 
  and (loc_code like 'H0%' or loc_code like 'B%')
  and REGEXP_LIKE(SUBSTR(loc_code,3), '^[[:digit:]]+$')
group by ROLLUP(loc_code, ship_lab, SKU, size_code) -- 用ROLLUP替代普通GROUP BY
HAVING COUNT(SKU) > 10 or GROUPING(loc_code) = 1 -- 保留符合条件的分组行和总计行
ORDER BY Loc_code asc;

GROUPING(字段)返回1表示该行是该字段的汇总行，0表示是原始分组行。

方式2：用UNION ALL手动拼接总计行 #

如果数据库不支持ROLLUP，可以先查询分组结果，再单独计算总计行，用UNION ALL拼接：

-- 分组数据
select 
  loc_code, ship_lab, size_code, sku,
  COUNT(SKU) as sku_count,
  SUM(stock_qty) as total_stock
from sherwin.prdinv
where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') 
  and (loc_code like 'H0%' or loc_code like 'B%')
  and REGEXP_LIKE(SUBSTR(loc_code,3), '^[[:digit:]]+$')
group by loc_code, ship_lab, SKU, size_code
HAVING COUNT(SKU) > 10

UNION ALL

-- 总计行
select 
  '总计' as loc_code, '' as ship_lab, '' as size_code, '' as sku,
  COUNT(SKU) as sku_count,
  SUM(stock_qty) as total_stock
from sherwin.prdinv
where sku IN ('B28W8030', 'B20W12651', 'A87W1351', 'B31W4651', 'A6W151') 
  and (loc_code like 'H0%' or loc_code like 'B%')
  and REGEXP_LIKE(SUBSTR(loc_code,3), '^[[:digit:]]+$')
ORDER BY 
  case when loc_code = '总计' then 1 else 0 end, -- 让总计行排在最后
  loc_code asc;

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