MSSQL分组聚合求和：含NULL/0价格的分类返回0

需求说明 #

需在MSSQL中对Product表按Category分组计算价格总和，若某分类下存在价格为NULL或0的商品，该分类的求和结果直接返回0。

测试数据准备 #

创建表及插入测试数据的代码：

CREATE TABLE Categories (
    Id int IDENTITY(1,1) PRIMARY KEY,
    Name varchar(255) NOT NULL
);

INSERT INTO Categories (Name) VALUES ('Fruits') ,('Electronics'),('Clothes'),('Furnitures');

CREATE TABLE Products (
    Name varchar(255) NOT NULL,
    Price decimal(18,2),
    CategoryId int FOREIGN KEY REFERENCES Categories(Id)
);

INSERT INTO Products (Name,Price,CategoryId) VALUES
('Oranges',2,1),
('Mangoes',NULL,1),
('Bananas',10,1),
('Dell',700,2),
('Samsung',200,2),
('Hp',800,2),
('Apples',20,1),
('Ginger',NULL,1),
('Sweater',220,3),
('Jeans',110,3),
('Door frames',200,4),
('Window Frames',100,4),
('Bed',1000,4),
('Chair',NULL,4)

注：原代码中缺少Products表的创建语句，此处补充完整。

现有查询问题 #

执行以下分组求和语句时，MSSQL会自动忽略NULL值计算总和，不符合需求（例如Fruits分类因存在NULL价格，需返回0而非32）：

SELECT Categories.name , SUM( Products.price) FROM  Products INNER JOIN Categories ON (Categories.id = Products.categoryid)
GROUP BY Categories.name

解决方案 #

可以通过CASE结合EXISTS子查询实现需求，先判断分类下是否存在违规价格（NULL或0），再决定返回0还是正常求和：

SELECT 
    c.Name,
    CASE 
        -- 检查当前分类下是否存在价格为NULL或0的商品
        WHEN EXISTS (
            SELECT 1 
            FROM Products p2 
            WHERE p2.CategoryId = c.Id 
              AND (p2.Price IS NULL OR p2.Price = 0)
        )
        THEN 0
        ELSE SUM(p.Price)
    END AS TotalPrice
FROM Categories c
INNER JOIN Products p ON c.Id = p.CategoryId
GROUP BY c.Id, c.Name

逻辑说明 #

1. EXISTS子查询会快速检查当前分组的分类下，是否有商品满足价格为NULL或0的条件，一旦找到匹配记录就停止查询，性能较高。
2. 如果存在违规价格，CASE分支返回0；否则计算该分类下所有有效价格的总和。
3. GROUP BY包含c.Id是为了避免分类名称重复时出现分组错误，保证逻辑严谨性。

替代实现方式 #

也可以通过聚合函数判断是否存在违规值，写法如下：

SELECT 
    c.Name,
    CASE 
        WHEN MAX(CASE WHEN p.Price IS NULL OR p.Price = 0 THEN 1 ELSE 0 END) = 1
        THEN 0
        ELSE SUM(p.Price)
    END AS TotalPrice
FROM Categories c
INNER JOIN Products p ON c.Id = p.CategoryId
GROUP BY c.Id, c.Name

这种方式通过MAX(CASE...)标记是否有违规商品，若最大值为1则说明存在，返回0；否则求和。

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