Bugl — Daily coding puzzles and drills

The SQL SUM() Function

The SUM() function is used to calculate the total sum of values within a specified numeric column. The SUM() function ignores NULL values in the column. The following SQL returns the sum of the Quantity field in the "OrderDetails" table:

Example

SELECT SUM(Quantity)

FROM OrderDetails;

SUM() Syntax

Select Sum(

column_name )

From

table_name

Where

condition

Demo Database

Below is a selection from the

OrderDetails table used in the examples:

OrderDetailID

OrderID

ProductID

Quantity

10248 11 12

10248 42 10

10248 72

10249 14

10249 51 40

Add a WHERE Clause

You can add a WHERE

clause to specify conditions. The following SQL returns the sum of the Quantity field for the product with

Formula

ProductID = 11, in the "OrderDetails" table:

Example

SELECT SUM(Quantity)

FROM OrderDetails

WHERE ProductId = 11;

Use an Alias

Give the summarized column a name by using the AS keyword.

Example

Name the column "total":

SELECT SUM(Quantity) AS total

FROM OrderDetails;

Use SUM() with GROUP BY

Here we use the SUM() function and the GROUP BY

clause, to return the Quantity for EACH OrderID in the "OrderDetails" table:

Example

SELECT OrderID, SUM(Quantity) AS [Total Quantity]

FROM OrderDetails

GROUP BY OrderID;

You will learn more about the GROUP BY clause later in this tutorial.