Aggregate Functions - sum

Article
04/03/2023

Applies to: SQL Server

Returns the sum of a sequence of numbers.

Syntax

  
fn:sum($arg as xdt:anyAtomicType*) as xdt:anyAtomicType

Arguments

$arg
Sequence of atomic values whose sum is to be computed.

Remarks

All types of the atomized values that are passed to sum() have to be subtypes of the same base type. Base types that are accepted are the three built-in numeric base types or xdt:untypedAtomic. Values of type xdt:untypedAtomic are cast to xs:double. If there is a mixture of these types, or if other values of other types are passed, a static error is raised.

The result of sum() receives the base type of the passed in types such as xs:double in the case of xdt:untypedAtomic, even if the input is optionally the empty sequence. If the input is statically empty, the result is 0 with the static and dynamic type of xs:integer.

The sum() function returns the sum of the numeric values. If an xdt:untypedAtomic value cannot be cast to xs:double, the value is ignored in the input sequence, $arg. If the input is a dynamically calculated empty sequence, the value 0 of the used base type is returned.

The function returns a runtime error when an overflow or out of range exception occurs.

Examples

This topic provides XQuery examples against XML instances that are stored in various xml type columns in the AdventureWorks2022 database.

A. Using the sum() XQuery function to find the total combined number of labor hours for all work center locations in the manufacturing process

The following query finds the total labor hours for all work center locations in the manufacturing process of all product models for which manufacturing instructions are stored.

SELECT Instructions.query('         
   declare namespace AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";         
  <ProductModel PMID= "{ sql:column("Production.ProductModel.ProductModelID") }"         
  ProductModelName = "{ sql:column("Production.ProductModel.Name") }" >         
   <TotalLaborHrs>         
     { sum(//AWMI:Location/@LaborHours) }         
   </TotalLaborHrs>         
 </ProductModel>         
    ') as Result         
FROM Production.ProductModel         
WHERE Instructions is not NULL

This is the partial result.

<ProductModel PMID="7" ProductModelName="HL Touring Frame">  
   <TotalLaborHrs>12.75</TotalLaborHrs>  
</ProductModel>  
<ProductModel PMID="10" ProductModelName="LL Touring Frame">  
  <TotalLaborHrs>13</TotalLaborHrs>  
</ProductModel>  
...

Instead of returning the result as XML, you can write the query to generate relational results, as shown in the following query:

SELECT ProductModelID,         
        Name,         
        Instructions.value('declare namespace   
      AWMI="https://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions";         
    sum(//AWMI:Location/@LaborHours)', 'float') as TotalLaborHours         
FROM Production.ProductModel         
WHERE Instructions is not NULL

This is a partial result:

ProductModelID Name                 TotalLaborHours         
-------------- -------------------------------------------------  
7              HL Touring Frame           12.75                   
10             LL Touring Frame           13                      
43             Touring Rear Wheel         3                       
...

Implementation Limitations

These are the limitations:

Only the single argument version of sum() is supported.
If the input is a dynamically calculated empty sequence, the value 0 of the used base type is returned instead of type xs:integer.
The sum() function maps all integers to xs:decimal.
The sum() function on values of type xs:duration is not supported.
Sequences that mix types across base type boundaries are not supported.
The sum((xs:double("INF"), xs:double("-INF"))) raises a domain error.

Share via