Publishers of technology books, eBooks, and videos for creative people

Home > Articles

  • Print
  • + Share This
This chapter is from the book

This chapter is from the book

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.3Figure 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’.

  • + Share This
  • 🔖 Save To Your Account