| title | description | author | ms.author | ms.date | ms.service | ms.subservice | ms.topic | f1_keywords | helpviewer_keywords | dev_langs | monikerRange | |||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SUM (Transact-SQL) | SUM (Transact-SQL) | MikeRayMSFT | mikeray | 06/28/2024 | sql | t-sql | reference |
|
|
| >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]
Returns the sum of all the values, or only the DISTINCT values, in the expression. SUM can be used with numeric columns only. Null values are ignored.
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
-- Aggregate Function Syntax SUM ( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax SUM ( [ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause) Applies the aggregate function to all values. ALL is the default.
Specifies that SUM returns the sum of unique values.
A constant, column, or function, and any combination of arithmetic, bitwise, and string operators. expression is an expression of the exact numeric or approximate numeric data type category, except for the bit data type. Aggregate functions and subqueries aren't permitted. For more information, see Expressions (Transact-SQL).
partition_by_clause divides the result set produced by the FROM clause into partitions to which the function is applied. If not specified, the function treats all rows of the query result set as a single group. order_by_clause determines the logical order in which the operation is performed. order_by_clause is required. For more information, see OVER Clause (Transact-SQL).
Returns the summation of all expression values in the most precise expression data type.
| Expression result | Return type |
|---|---|
| tinyint | int |
| smallint | int |
| int | int |
| bigint | bigint |
| decimal category (p, s) | decimal(38, s) |
| money and smallmoney category | money |
| float and real category | float |
SUM is a deterministic function when used without the OVER and ORDER BY clauses. It's nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions. Also, SUM might appear to behave as a nondeterministic function when you use it with float and real data types. But the underlying reason is the approximate nature of these data types.
The following examples show using the SUM function to return summary data in the [!INCLUDE ssSampleDBnormal] database.
SELECT Color, SUM(ListPrice), SUM(StandardCost) FROMProduction.ProductWHERE Color IS NOT NULLAND ListPrice !=0.00AND Name LIKE'Mountain%'GROUP BY Color ORDER BY Color; GO[!INCLUDE ssResult]
Color --------------- --------------------- --------------------- Black 27404.84 5214.9616 Silver 26462.84 14665.6792 White 19.00 6.7926 (3 row(s) affected) The following example uses the SUM function with the OVER clause to provide a cumulative total of yearly sales for each territory in the Sales.SalesPerson table in the [!INCLUDE ssSampleDBnormal] database. The data is partitioned by TerritoryID and logically ordered by SalesYTD. This means that the SUM function is computed for each territory based on the sales year. For TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. The cumulative total sales value for these two rows is computed and then the third row representing sales for the year 2006 is included in the computation.
SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (PARTITION BY TerritoryID ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROMSales.SalesPersonWHERE TerritoryID IS NULLOR TerritoryID <5ORDER BY TerritoryID,SalesYear;[!INCLUDE ssResult]
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal ---------------- ----------- ----------- -------------------- -------------------- -------------------- 274 NULL 2005 559,697.56 559,697.56 559,697.56 287 NULL 2006 519,905.93 539,801.75 1,079,603.50 285 NULL 2007 172,524.45 417,375.98 1,252,127.95 283 1 2005 1,573,012.94 1,462,795.04 2,925,590.07 280 1 2005 1,352,577.13 1,462,795.04 2,925,590.07 284 1 2006 1,576,562.20 1,500,717.42 4,502,152.27 275 2 2005 3,763,178.18 3,763,178.18 3,763,178.18 277 3 2005 3,189,418.37 3,189,418.37 3,189,418.37 276 4 2005 4,251,368.55 3,354,952.08 6,709,904.17 281 4 2005 2,458,535.62 3,354,952.08 6,709,904.17 (10 row(s) affected) In this example, the OVER clause doesn't include PARTITION BY. This means that the function will be applied to all rows returned by the query. The ORDER BY clause specified in the OVER clause determines the logical order to which the SUM function is applied. The query returns a cumulative total of sales by year for all sales territories specified in the WHERE clause. The ORDER BY clause specified in the SELECT statement determines the order in which the rows of the query are displayed.
SELECT BusinessEntityID, TerritoryID ,DATEPART(yy,ModifiedDate) AS SalesYear ,CONVERT(VARCHAR(20),SalesYTD,1) AS SalesYTD ,CONVERT(VARCHAR(20),AVG(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) ),1) AS MovingAvg ,CONVERT(VARCHAR(20),SUM(SalesYTD) OVER (ORDER BY DATEPART(yy,ModifiedDate) ),1) AS CumulativeTotal FROMSales.SalesPersonWHERE TerritoryID IS NULLOR TerritoryID <5ORDER BY SalesYear;[!INCLUDE ssResult]
BusinessEntityID TerritoryID SalesYear SalesYTD MovingAvg CumulativeTotal ---------------- ----------- ----------- -------------------- -------------------- -------------------- 274 NULL 2005 559,697.56 2,449,684.05 17,147,788.35 275 2 2005 3,763,178.18 2,449,684.05 17,147,788.35 276 4 2005 4,251,368.55 2,449,684.05 17,147,788.35 277 3 2005 3,189,418.37 2,449,684.05 17,147,788.35 280 1 2005 1,352,577.13 2,449,684.05 17,147,788.35 281 4 2005 2,458,535.62 2,449,684.05 17,147,788.35 283 1 2005 1,573,012.94 2,449,684.05 17,147,788.35 284 1 2006 1,576,562.20 2,138,250.72 19,244,256.47 287 NULL 2006 519,905.93 2,138,250.72 19,244,256.47 285 NULL 2007 172,524.45 1,941,678.09 19,416,780.93 (10 row(s) affected) Examples: [!INCLUDE ssazuresynapse-md] and [!INCLUDE ssPDW]
The following example returns the total number of each product sold in the year 2003.
-- Uses AdventureWorksSELECT ProductKey, SUM(SalesAmount) AS TotalPerProduct FROMdbo.FactInternetSalesWHERE OrderDateKey >='20030101'AND OrderDateKey <'20040101'GROUP BY ProductKey ORDER BY ProductKey;Here's a partial result set.
ProductKey TotalPerProduct ---------- --------------- 214 31421.0200 217 31176.0900 222 29986.4300 225 7956.1500 The following example calculates the sum of the ListPrice and StandardCost for each color listed in the Product table.
-- Uses AdventureWorksSELECT Color, SUM(ListPrice)AS TotalList, SUM(StandardCost) AS TotalCost FROMdbo.DimProductGROUP BY Color ORDER BY Color;The first part of the result set is shown below:
Color TotalList TotalCost ---------- ------------- -------------- Black 101295.7191 57490.5378 Blue 24082.9484 14772.0524 Grey 125.0000 51.5625 Multi 880.7468 526.4095 NA 3162.3564 1360.6185 