[SQL Basic] Let’s use some Numeric SQL functions — TRUNCATE, ROUND, MOD

SQLGate

19 Oct 2018 • 7 min read

#18. Marketing/Data Analysis for Beginners

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, use the links in our Glossary to refresh your memory.

#Glossary:

📌Lesson 3: SELECT / FROM / WHERE
📌Lesson 4: ORDER BY
📌Lesson 5: AND / OR / IN
📌Lesson 6: LIKE
📌Lesson 7: AS /DISTINCT / IS NULL
📌Lesson 9: COUNT / SUM
📌Lesson 10: AVG / MAX / MIN
📌Lesson 11: GROUP BY / HAVING
📌Lesson 12: CASE
📌Lesson 13: INNER JOIN
📌Lesson 14: OUTER LEFT JOIN / OUTER RIGHT JOIN
📌Lesson 15: SUBQUERIES
📌Lesson 16: DATE_FORMAT / DATEDIFF
📌Lesson 17: CONCAT/LENGTH/SUBSTR

#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 as 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 Function 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.

Okay, let’s start with our query.

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 the SUM function 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)

Now let’s assume you want to average the amount paid per customer. We’re going to use the AVG function this time, and we will use the ROUND function to round off decimal points instead of cutting them.

📕 ROUND Function 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, 3)➡️ We specified 1here, 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 Function 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. Let’s use the primary key to split the customers into 3 groups.

Start with the SELECT function:

SELECT *

Use MOD to calculate and split the customers into 3 groups:

MOD(payment_id, 3) AS sep_group

Close with the FROM function:

FROM payment;

Your final query should look like this:

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