## Calculating a Sum with `SUM()`

Use the aggregate function `SUM() `to find the sum (total) of a set of values.

#### To calculate the sum of a set of values:

Type:

SUM(

*expr*)`expr`is a column name, literal, or numeric expression. The result’s data type is at least as precise as the most precise data type used in`expr`.

**Listing 6.3 **and **Figure 6.3 **show some queries that involve `SUM()`. The first query returns the total advances paid to all authors. The second query returns the total sales of books published in 2000. The third query returns the total price, sales, and revenue (= price ´ sales) of all books. Note a mathematical chestnut in action here: “The sum of the products doesn’t (necessarily) equal the product of the sums.”

#### Listing 6.3 Some SUM() queries. See Figure 6.3 for the results.

SELECT SUM(advance) AS "Total advances" FROM royalties; SELECT SUM(sales) AS "Total book sales for 2000" FROM titles WHERE pubdate BETWEEN DATE '2000-01-01' AND DATE '2000-12-31'; SELECT SUM(price) AS "Total price", SUM(sales) AS "Total sales", SUM(price * sales) AS "Total revenue" FROM titles;

**Figure 6.3 Results of Listing 6.3.**

**TIPS**

`SUM()`works with only numeric data types.The sum of no rows is null—not zero as you might expect.

In

**Microsoft Access**date literals, omit the`DATE`keyword, and surround the literal with`#`characters instead of quotes. To run Listing 6.3 in Access, change the date literals in the second query to`#2000-01-01#`and`#2000-12-31#`. In**Microsoft SQL Server**date literals, omit the`DATE`keyword. To run Listing 6.3 in SQL Server, change the date literals to`‘2000-01-01’`and`‘2000-12-31’`.