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 results 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 doesnt (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 nullnot 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.