# MySQL SUM()

The `SUM()`

aggregate function returns the sum of an expression.

If the return set has no rows, the `SUM()`

function returns `NULL`

.

Consider the following result set:

You can see that the `film`

table contains a column for storing the length (in minutes) of each film.

We could use the `SUM()`

function to return the total length of all films combined.

Result:

You could even divide that number by 60 to give you the number of hours (we also round it with `ROUND()`

):

Result:

Maybe the movie rental provider could use this info to boast that they have "Over 1920 hours worth of movies!".

## Another Example

Consider the following result set:

The above table stores customers' payment data. Each time a customer makes a payment, a new record is added to this table with the payment details.

In particular, look at the `customer_id`

and the `payment`

columns. You can see that there are many records containing the same `customer_id`

, each with a corresponding `amount`

.

What if you wanted to add up the total of all entries in the `amount`

column?

We could construct the following query:

Result:

This tells us that customer 1 has spent a total of $118.68.

## All Customers

We could extend the previous example to display all customers who've made a payment. We could display one record for each user, along with the total amount that they've paid in movie rentals. Like this:

Result:

In the above example, we use an `INNER JOIN`

to return data from two tables (so that we can display their names along with their totals). We also provide a column heading using the `AS`

clause. We use `GROUP BY`

so that we can list one record for each customer, combined with their total paid. And of course, the `SUM()`

function is used to provide each total.

## Add `HAVING`

and `ORDER BY`

We could extend the above example even further by adding the `HAVING`

and `ORDER BY`

clauses.

Example:

Result:

In the above example, we return only those records with where the total amount exceeds 180, then order it by the amount in descending order (so that the customer who's spent the most is listed at the top).

The examples on this page use the Sakila sample database.