Now that we’ve learned a few things about strings, let’s try some numeric functions. There are many kinds of numeric functions we’ve already covered in previous lessons (**AVG/MIN/MAX/SUM**) and we’re going to use them again in this tutorial.

If you need a review, take a few moments to glance over our previous articles.

### #Table of Contents

Query 1. For each customer, calculate the total amount of rent paid and trim the decimal result. (TRUNCATE function)

`SELECT customer_id, TRUNCATE(SUM(amount), 0) AS total_payment FROM payment GROUP BY customer_id;`

Query 2. Let’s get the average rental fee paid per customer and round it up at the second decimal. (ROUND function)

`SELECT customer_id, ROUND(AVG(amount), 2) AS avg_payment FROM payment GROUP BY customer_id;`

Query 3. Split the results of the ‘payment_id’ column into 3 different groups. (MOD function)

`SELECT payment_id, customer_id, staff_id, rental_id, amount, payment_date, last_update, MOD(payment_id, 3) AS sep_group FROM payment;`

Before we start with our queries, let’s check out the payment table.

You’ll find these columns in the payment table:

- Payment ID (*payment_id*)

- Customer ID (*customer_id*)

- Staff ID (*staff_id*)

- Rental ID (*rental_id*)

- Amount (*amount*)

- Payment Date (*payment_date*)

- Last Update (*last_update*)

### ⭐️Tip

🍏 **Primary key**: uniquely identify a record in a database table

🍎 **Foreign key**: used to link two tables together

In the payment table, the payment ID is considered to be the Primary Key. The columns *customer_id*, *staff_id*, and *rental_id* are Foreign keys. If we want to combine any tables in the future, we would be using these columns to do so.

### #TRUNCATE

**Q1. For each customer, calculate the total amount of rent paid and round the decimal result. (TRUNCATE function)**

We’re going to dive into the payment table again. In this table, we can see which customers rented which movies.

Some customers rent several different movies, and each movie has a different price. So in order to find the total a customer paid for movies rented, we need to do some math with our numeric functions. After we calculate the total, we will use `TRUNCATE`

to trim the number to the nearest specified decimal.

📕 `TRUNCATE`

examples:

1️⃣ `SELECT TRUNCATE(118.68124, 3)`

➡️ We specified `3`

here, so the result will be cut to the 3rd decimal = `118.681`

2️⃣ `SELECT TRUNCATE(118.68124, 1)`

➡️ We specified `1`

here, so the result will be cut to the 1st decimal = `118.6`

3️⃣ `SELECT TRUNCATE(118.68124, -1)`

➡️ We specified `-1`

here, and the result will be cut so there is no decimal, and no number in the tenth position= `110`

The `TRUNCATE`

function does not increase or decrease the decimal point. It doesn’t round the result up or down, it only trims down the number by cutting the decimals to the position we want. As seen above, you can also use the minus sign (`-`

) to indicate the position before the decimal point in the desired position.

Start your query with the `SELECT`

function and the *customer_id* column:

`SELECT customer_id`

Now to find the sum of rent paid by each customer, we’re going to use `SUM`

to calculate the total amount and `TRUNCATE`

to trim the result:

`TRUNCATE(SUM(amount), 0) AS total_payment`

Finish with `FROM`

and the table name, then organize it with `GROUP BY`

:

`FROM payment GROUP BY customer_id;`

Your final query should look like this:

`SELECT customer_id, TRUNCATE(SUM(amount), 0) AS total_payment FROM payment GROUP BY customer_id;`

We can substitute other functions to calculate the amount, but `TRUNCATE`

is needed to trim the decimals and get a whole number.

### #ROUND

**Q2. Let’s get the average rental fee paid per customer and round it up at the second decimal. (ROUND function)**

This time let’s say you want to average the amount paid per customer. We’re going to use `AVG`

this time, and we will use `ROUND`

to round off decimal points instead of cutting them.

📕 `ROUND`

examples:

1️⃣ `SELECT ROUND(3.708750, 3)`

➡️ We specified `3`

here, so the result will be rounded to the 3rd decimal = `3.709`

2️⃣ `SELECT ROUND(3.708750, 1)`

➡️ We specified `1`

here, so the result will be rounded to the 1st decimal = `3.7`

A negative (`-`

) can also be used for the desired rounding, which indicates the position before the decimal point. Now to start our second query.

As always, start with `SELECT`

:

`SELECT customer_id`

Use `AVG`

to get the average of the total amount, and `ROUND`

to round the decimals up to the second (`2`

) position. Use `AS`

to rename the column:

`ROUND(AVG(amount), 2) AS avg_payment`

Finish the query with `FROM`

and use `GROUP BY`

to organize the results. Your final query will look like this:

`SELECT customer_id, ROUND(AVG(amount), 2) AS avg_payment FROM payment GROUP BY customer_id;`

Great! Now we have a rounded, more concise average of how much each customer paid for their movie rentals.

### #MOD

**Q3. Split the results of the ‘payment_id’ column into 3 different groups. (MOD function)**

In the next query, we are going to split the long list of customers into three groups so we can better view and understand the information. We will do this using the `MOD`

function. This function does two things: first, it divides the first number you indicate by the second number. Then, it prints the remaining amount.

📕 `MOD`

examples:

1️⃣ `SELECT MOD(100, 3)`

➡️ 100 will be divided by 3, which normally gives us 33.333333~. Ignoring the decimal for a moment, 33 multiplied by 3 is **99**, which is the closest number to 100 without going over. The difference between 99 and 100 is `1`

, which is the result that is printed.

2️⃣ `SELECT MOD(100, 4)`

➡️ 100 will be divided by 4. We get `0`

here, because 100 can be divided perfectly by 4. There is no remaining amount.

3️⃣ `SELECT MOD(100, 6)`

➡️ 100 will be divided by 6. If we just divide, we would get `16.666666666`

~. So removing that decimal, 16 multiplied by 6 is 96. The difference between 100 and 96 is 4, so the printed result is `4`

.

This function is useful when you want to know the exact remaining amount after dividing. Let’s see how this works in an example.

In the payment table, we have 16,049 rows in total. And the *payment_id* column starts at 1 and ends at 16,049. Using this column we can assure there will be no duplicated information, which makes it a Primary Key. We will use this primary key to split the customers into 3 groups.

Start with `SELECT`

: `SELECT *`

Use MOD to calculate and split the customers into 3 groups: `MOD(payment_id, 3) AS sep_group`

Close with `FROM`

: `FROM payment;`

Your final query should look like this:

`SELECT *, MOD(payment_id, 3) AS sep_group FROM payment;`

We can also use `COUNT`

along with `MOD`

to find how many customers are in each group:

`SELECT MOD(payment_id, 3) AS sep_group, COUNT(MOD(payment_id, 3)) AS count_group FROM payment GROUP BY MOD(payment_id, 3);`