How can I calculate the cost given the discount% in web2py/sqlite?

Question

I have two tables, a product table which contains:

Product
-----------
ProductName
Supplier,'reference supplier'
sku
Description
Price

then I have a supplier table

Supplier
---------
SupplierName
Discount

My question is, how can I calculate in sqlite the price based on the discount? The price will be dynamic and that is why I think its a better idea to do the calculation upon retrieval of the product. That way if the Discount changes, the price changes.

Is the discount stored a decimal (0.10) or a percent (10, meaning 10% off)? — OneCricketeer, Jan 12 '16 at 22:02
it could be either or. In the tables as they are, I have them 10 meaning 10% — weemo, Jan 12 '16 at 22:04
Okay, so are you familiar with SQL and what have you tried? The math would be `DiscountedPrice = Price * (1 - Discount)` — OneCricketeer, Jan 12 '16 at 22:06
I should have added in the question that the whole purpose is to have to change the discount% and have the price change accordingly upon retrieval. So. I am a bit confused/lost whether I need the fields or not. I previously had it so the calculation would take place at the DAL level — weemo, Jan 12 '16 at 22:09
If you used an ORM library like peewee or SQLAlchemy you could do it at the DAL — OneCricketeer, Jan 12 '16 at 22:12
It's an efficiency vs language decision, @weemo, DAL is good if you want to be lazy and not do the query yourself. On the other hand, SQL is often tedious to write and difficult to get correct, especially when dealing with more than a few tables — hd1, Jan 12 '16 at 22:16
@cricket_007 and my comment was not meant to attack, just point out that DAL is just a way for programmers to be lazy and not tune their queries — hd1, Jan 12 '16 at 22:20

score 0 · Accepted Answer · answered Jan 12 '16 at 22:10

0

SELECT Price * (1 - Discount/100.0) as final_price from Product p join Supplier s on s.supplier_name = p.supplier where productname = ?

Should sort you.

answered Jan 12 '16 at 22:10

hd1

33,938
5
80
91

How can I calculate the cost given the discount% in web2py/sqlite?

1 Answers1