SUM (Transact-SQL)
Retourne la somme de toutes les valeurs, ou uniquement des valeurs distinctes (DISTINCT) de l'expression. Les fonctions SUM ne s'emploient que sur des colonnes de type numérique. Les valeurs NULL sont ignorées.
S'applique à : SQL Server (SQL Server 2008 via la version actuelle, Base de données SQL Windows Azure (version initiale via la version actuelle. |
.gif "Icône Lien de rubrique")
Conventions de syntaxe Transact-SQL
Syntaxe
SUM ( [ ALL | DISTINCT ] expression )
OVER ( [ partition_by_clause ] order_by_clause )
Arguments
ALL
Applique la fonction d'agrégation à toutes les valeurs. ALL est l'argument par défaut.DISTINCT
Précise que la fonction SUM doit retourner la somme des valeurs uniques.expression
Constante, colonne ou fonction, et toute combinaison d'opérateurs arithmétiques, de type chaîne ou binaire. expression est une expression de la catégorie de type de données valeur numérique exacte ou approchée, sauf pour le type de données bit. Les fonctions d'agrégation et les sous-requêtes ne sont pas autorisées. Pour plus d'informations, consultez Expressions (Transact-SQL).OVER ( [ partition_by_clause ] order_by_clause**)**
partition_by_clause divise le jeu de résultats généré par la clause FROM en partitions auxquelles la fonction est appliquée. S'il n'est pas spécifié, la fonction gère toutes les lignes du jeu de résultats de la requête en un seul groupe. order_by_clause détermine l'ordre logique dans lequel l'opération est effectuée. order_by_clause est requis. Pour plus d'informations, consultez Clause OVER (Transact-SQL).
Types de retours
Retourne la somme de toutes les valeurs de expression dans le type de données expression le plus précis.
Résultat de l'expression |
Type de retour |
|---|---|
tinyint |
int |
smallint |
int |
int |
int |
bigint |
bigint |
catégorie decimal (p, s) |
decimal(38, s) |
Catégories money et smallmoney |
money |
catégories float et real |
float |
Notes
SUM est une fonction déterministe lorsqu'elle est utilisée sans les clauses OVER et ORDER BY. Elle n'est pas déterministe avec les clauses OVER et ORDER BY. Pour plus d'informations, consultez Fonctions déterministes et non déterministes.
Exemples
A.Utilisation de SUM pour retourner des données de synthèse
Les exemples suivants illustrent l'utilisation de la fonction SUM pour renvoyer des données de synthèse dans la base de données AdventureWorks2012.
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
Voici l'ensemble des résultats.
Color
--------------- --------------------- ---------------------
Black 27404.84 5214.9616
Silver 26462.84 14665.6792
White 19.00 6.7926
(3 row(s) affected)
B.Utilisation de la clause OVER
L'exemple suivant utilise la fonction SUM avec la clause OVER pour fournir un total cumulé des ventes annuelles pour chaque secteur dans la table Sales.SalesPerson de la base de données AdventureWorks2012. Les données sont partitionnées par TerritoryID et classées logiquement par SalesYTD. Cela signifie que la fonction SUM est calculée pour chaque secteur selon l'année de vente. Notez que pour TerritoryID 1, il y a deux lignes pour l'année 2005 représentant les deux vendeurs avec leurs ventes de l'année. Les ventes cumulées pour ces deux lignes sont calculées et la troisième ligne représentant les ventes de l'année 2006 est incluse dans le calcul.
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;
Voici l'ensemble des résultats.
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)
Dans cet exemple, la clause OVER n'inclut pas PARTITION BY. Cela signifie que la fonction sera appliquée à toutes les lignes retournées par la requête. La clause ORDER BY spécifiée dans la clause OVER détermine l'ordre logique selon lequel la fonction SUM est appliquée. La requête retourne le total cumulé des ventes par année pour tous les secteurs de vente spécifiés dans la clause WHERE. La clause ORDER BY spécifiée dans l'instruction SELECT détermine l'ordre dans lequel les lignes de la requête sont affichées.
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;
Voici l'ensemble des résultats.
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)