SQL分组带HAVING条件查询：计算符合条件的条目总数量与value字段总和

注意事项 #

你原有SQL中的结束日期存在格式错误，2021-31-08需修正为2021-08-31避免查询报错，以下方案默认按筛选8月整月数据编写，你可以根据实际需求调整时间范围。

需求1：计算summe字段总和（返回结果4） #

将你现有查询作为子查询，外层直接对summe列求和即可：

SELECT SUM(summe) AS total_count
FROM (
    SELECT adrnr, COUNT(adrnr) as summe 
    FROM tablename 
    WHERE datum >= '2021-08-01 00:00:00.000' 
      AND datum <= '2021-08-31 23:59:59.999' 
    GROUP BY adrnr 
    HAVING COUNT(adrnr) > 1
) AS t;

需求2：统计符合条件的条目value总和（返回结果82.12） #

写法1：子查询匹配符合条件的adrnr #

SELECT SUM(value) AS total_value
FROM tablename
WHERE datum >= '2021-08-01 00:00:00.000' 
  AND datum <= '2021-08-31 23:59:59.999'
  AND adrnr IN (
    SELECT adrnr
    FROM tablename 
    WHERE datum >= '2021-08-01 00:00:00.000' 
      AND datum <= '2021-08-31 23:59:59.999' 
    GROUP BY adrnr 
    HAVING COUNT(adrnr) > 1
);

写法2：窗口函数实现（无需重复写过滤条件，性能更优） #

SELECT SUM(value) AS total_value
FROM (
    SELECT 
        value,
        COUNT(*) OVER (PARTITION BY adrnr) AS adrnr_count
    FROM tablename
    WHERE datum >= '2021-08-01 00:00:00.000' 
      AND datum <= '2021-08-31 23:59:59.999'
) AS t
WHERE adrnr_count > 1;

同时返回两个结果的实现 #

如果需要一次查询同时拿到总条目数和value总和，可以用如下写法：

SELECT 
    SUM(summe) AS total_count,
    SUM(total_value_per_adrnr) AS total_value
FROM (
    SELECT 
        adrnr,
        COUNT(adrnr) as summe,
        SUM(value) as total_value_per_adrnr
    FROM tablename 
    WHERE datum >= '2021-08-01 00:00:00.000' 
      AND datum <= '2021-08-31 23:59:59.999' 
    GROUP BY adrnr 
    HAVING COUNT(adrnr) > 1
) AS t;

内容的提问来源于stack exchange，提问作者Jens Körte