![]() This time let’s say you want to average the amount paid per customer. Let’s get the average rental fee paid per customer and round it up at the second decimal. We can substitute other functions to calculate the amount, but TRUNCATE is needed to trim the decimals and get a whole number. Your final query should look like this: SELECT customer_id, TRUNCATE(SUM(amount), 0) AS total_payment FROM payment GROUP BY customer_id TRUNCATE(SUM(amount), 0) AS total_paymentįinish with FROM and the table name, then organize it with GROUP BY: 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: Start your query with the SELECT function and the customer_id column: ![]() As seen above, you can also use the minus sign ( -) to indicate the position before the decimal point in the desired position. It doesn’t round the result up or down, it only trims down the number by cutting the decimals to the position we want. The TRUNCATE function does not increase or decrease the decimal point. After we calculate the total, we will use TRUNCATE to trim the number to the nearest specified decimal.ġ️⃣ SELECT TRUNCATE(118.68124, 3)➡️ We specified 3 here, so the result will be cut to the 3rd decimal = 118.681Ģ️⃣ SELECT TRUNCATE(118.68124, 1)➡️ We specified 1 here, so the result will be cut to the 1st decimal = 118.6ģ️⃣ 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 So in order to find the total a customer paid for movies rented, we need to do some math with our numeric functions. Some customers rent several different movies, and each movie has a different price. In this table, we can see which customers rented which movies. We’re going to dive into the payment table again. For each customer, calculate the total amount of rent paid and round the decimal result. If we want to combine any tables in the future, we would be using these columns to do so. The columns customer_id, staff_id, and rental_id are Foreign keys. In the payment table, the payment ID is considered to be the Primary Key. ![]() □ Foreign key: used to link two tables together □ Primary key: uniquely identify a record in a database table You’ll find these columns in the payment table: (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 īefore we start with our queries, let’s check out the payment table. Split the results of the ‘payment_id’ column into 3 different groups. (ROUND function) SELECT customer_id, ROUND(AVG(amount), 2) AS avg_payment FROM payment GROUP BY customer_id (TRUNCATE function) SELECT customer_id, TRUNCATE(SUM(amount), 0) AS total_payment FROM payment GROUP BY customer_id For each customer, calculate the total amount of rent paid and trim the decimal result. If you need a review, take a few moments to glance over our previous articles. 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. Now that we've learned a few things about strings let's try some numeric functions.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |