Is sum an aggregate function in sql

Starting here? This lesson is part of a full-length tutorial in using SQL for Data Analysis. Check out the beginning.

In this lesson we'll cover:

• The SQL SUM function
• Practice problem

The SQL SUM function

SUM is a SQL aggregate function. that totals the values in a given column. Unlike COUNT, you can only use SUM on columns containing numerical values.

The query below selects the sum of the volume column from the Apple stock prices dataset:

SELECT SUM(volume) FROM tutorial.aapl_historical_stock_price

An important thing to remember: aggregators only aggregate vertically. If you want to perform a calculation across rows, you would do this with simple arithmetic.

You don't need to worry as much about the presence of nulls with SUM as you would with COUNT, as SUM treats nulls as 0.

Sharpen your SQL skills

Practice Problem

Write a query to calculate the average opening price (hint: you will need to use both COUNT and SUM, as well as some simple arithmetic.).

Try it out See the answer

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Aggregate Functions (Transact-SQL)

• Article
• 08/25/2022
• 2 minutes to read

In this article

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

An aggregate function performs a calculation on a set of values, and returns a single value. Except for COUNT(*), aggregate functions ignore null values. Aggregate functions are often used with the GROUP BY clause of the SELECT statement.

All aggregate functions are deterministic. In other words, aggregate functions return the same value each time that they are called, when called with a specific set of input values. See Deterministic and Nondeterministic Functions for more information about function determinism. The OVER clause may follow all aggregate functions, except the STRING_AGG, GROUPING or GROUPING_ID functions.

Use aggregate functions as expressions only in the following situations:

• The select list of a SELECT statement (either a subquery or an outer query).
• A HAVING clause.

Transact-SQL provides the following aggregate functions:

• APPROX_COUNT_DISTINCT
• AVG
• CHECKSUM_AGG
• COUNT
• COUNT_BIG
• GROUPING
• GROUPING_ID
• MAX
• MIN
• STDEV
• STDEVP
• STRING_AGG
• SUM
• VAR
• VARP

See also

Built-in Functions (Transact-SQL)
OVER Clause (Transact-SQL)

Skip to main content

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

SUM (Transact-SQL)

• Article
• 05/27/2022
• 4 minutes to read

In this article

Applies to: SQL Server (all supported versions) Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

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.

Transact-SQL Syntax Conventions

Syntax

-- Aggregate Function Syntax SUM ( [ ALL | DISTINCT ] expression ) -- Analytic Function Syntax SUM ([ ALL ] expression) OVER ( [ partition_by_clause ] order_by_clause)

Arguments

ALL
Applies the aggregate function to all values. ALL is the default.

DISTINCT
Specifies that SUM returns the sum of unique values.

expression
Is 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 are not permitted. For more information, see Expressions (Transact-SQL).

OVER ( [ partition_by_clause ] order_by_clause)
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).

Return Types

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

Remarks

SUM is a deterministic function when used without the OVER and ORDER BY clauses. It is nondeterministic when specified with the OVER and ORDER BY clauses. For more information, see Deterministic and Nondeterministic Functions.

Examples

A. Using SUM to return summary data

The following examples show using the SUM function to return summary data in the AdventureWorks2019 database.

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

Here is the result set.

Color --------------- --------------------- --------------------- Black 27404.84 5214.9616 Silver 26462.84 14665.6792 White 19.00 6.7926 (3 row(s) affected)

B. Using the OVER clause

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 AdventureWorks2019 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. Notice that for TerritoryID 1, there are two rows for sales year 2005 representing the two sales people with sales that year. The cumulative sales total 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 FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY TerritoryID,SalesYear;

Here is the result set.

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 does not 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 FROM Sales.SalesPerson WHERE TerritoryID IS NULL OR TerritoryID < 5 ORDER BY SalesYear;

Here is the result set.

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: Azure Synapse Analytics and Analytics Platform System (PDW)

C. A simple SUM example

The following example returns the total number of each product sold in the year 2003.

-- Uses AdventureWorks SELECT ProductKey, SUM(SalesAmount) AS TotalPerProduct FROM dbo.FactInternetSales WHERE OrderDateKey >= '20030101' AND OrderDateKey < '20040101' GROUP BY ProductKey ORDER BY ProductKey;

Here is a partial result set.

ProductKey TotalPerProduct ---------- --------------- 214 31421.0200 217 31176.0900 222 29986.4300 225 7956.1500

D. Calculating group totals with more than one column

The following example calculates the sum of the ListPrice and StandardCost for each color listed in the Product table.

-- Uses AdventureWorks SELECT Color, SUM(ListPrice)AS TotalList, SUM(StandardCost) AS TotalCost FROM dbo.DimProduct GROUP 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

See Also

Aggregate Functions (Transact-SQL)
OVER Clause (Transact-SQL)

Is sum an aggregate function?

What are the 5 aggregate functions in SQL?

Is there a sum function in SQL?

Which are the aggregate functions in SQL?