How to calculate a discount?

Hi all. There are 3 tables - Discounts(Discount) Customers(Clients) and Orders(Orders)

Discount

``````id_discount count_orders percent_discount
1 5 1,5
2 10 2,5``````

Clients
``````id_client Name Surname
1 Ivan Petrov
2 Vasya Vasev``````

Orders
`id order_sum`

The question how to calculate the total amount, given the number of orders made by the client? If the total number of orders 5 and above, the discount is 1.5%, if 10 and above, 2.5%. Otherwise, no discount.
July 8th 19 at 15:31
July 8th 19 at 15:33
You need to the Orders table to add foreign key to table Clients. i.e. add a field clients_id. And then using inner join to contact the Discount table to calculate the total discount.
Yes, in the Orders table is the ID of the client. But I don't understand how to make the condition through SKL. Beginner - Isaias_Klock commented on July 8th 19 at 15:36
using inner join
select o.order_sum from clients as c
inner join orders as o on c.id=o.client_id - jonathan.Padbe commented on July 8th 19 at 15:39
: but what about the terms and counting the number of orders? Might be using if possible? - Isaias_Klock commented on July 8th 19 at 15:42
: Not necessary conditions. You do the same in each table Discount number of orders corresponds to the discount percentage, and that is value take. Again suseuses table Discount - jonathan.Padbe commented on July 8th 19 at 15:45
: is it not necessary? I don't quite understand how then going to work? Should be discounted when the number of orders is 5 and more than 5, but less than 10, and 10 or higher. I know you can use Count, but this example clearly can't make the script - Isaias_Klock commented on July 8th 19 at 15:48
So if you want to use terms here -www.sql-tutorial.ru/ru/book_case_statement/page1.html - jonathan.Padbe commented on July 8th 19 at 15:51
: how to contact the discount in your example? - Isaias_Klock commented on July 8th 19 at 15:54
Something like this
order_sum select*from percent_discount discount,orders
where count_orders =
(select count(client_id) from clients as c
inner join orders as o on c.id=o.client_id) - jonathan.Padbe commented on July 8th 19 at 15:57
: thanks, will test. This script will automatically select the % discount? Ie, if we have 6-7 orders from 1 customer, then the script will be multiplied by 1.5%? - Isaias_Klock commented on July 8th 19 at 16:00
: No, then you have to change on order_sum+=order_sum*percent_discount - jonathan.Padbe commented on July 8th 19 at 16:03
July 8th 19 at 15:35
count_orders do as the interval from 5 to 10 and from 10 to 100000000000000 for example, because otherwise, without functions and additional samples will be problematic
``````select
Discount.percent_discount
from
Discount
where
(select count(Orders.id_client) where id_client = ...) BETWEEN Discount.count_orders_min AND Discount.count_orders_max``````

And I would unite the fields, Name Surname , does it make sense to keep them separate?

Find more questions by tags FirebirdSQL