Sql 学习笔记

SUM (Transact-SQL)

返回表达式中所有值的和或仅非重复值的和。SUM 只能用于数字列。空值将被忽略。后面可能跟随 OVER 子句。

A. 在聚合和行聚合中使用 SUM

1、只提供汇总数据的聚合函数

USE AdventureWorks;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
WHERE Color IS NOT NULL AND ListPrice != 0.00 AND Name LIKE 'Mountain%'
GROUP BY Color
ORDER BY Color;
GO

2、提供详尽数据和汇总数据的行聚合函数。

USE AdventureWorks;
GO
SELECT Color, ListPrice, StandardCost
FROM Production.Product
WHERE Color IS NOT NULL AND ListPrice != 0.00 AND Name LIKE 'Mountain%'
ORDER BY Color
COMPUTE SUM(ListPrice), SUM(StandardCost) BY Color;
GO

B. 计算多列的组合计

USE AdventureWorks;
GO
SELECT Color, SUM(ListPrice), SUM(StandardCost)
FROM Production.Product
GROUP BY Color
ORDER BY Color
GO