Hi there guys!
Just went through the content and got thinking how best for my scenario to the following:
Assume having a Sales table with the following fields:
Sales Order ID
Sales Order Item
And another table named Discounts with the following structure:
Sales Order ID
For a customer and a sales order there can exist a discount value in the Discounts table, however there is no information about the precise discount value per each item in the Sales table. Thus, for this exercise it is necessary to:
a)Define Create Table statements for both tables.
b)Define a strategy to allocate the discount values to the Sales table (e.g., in a new column) and define the necessary query/scripts to execute the logic.
Hi, welcome to the forums.
Is there an associated lesson for this? Can you link to it?
Are you getting an error? What’s the issue?
Thanks for the answer.
There isnt a specific lesson associated with my question.
I just would like to explore how to build and explore that scenario.
Any ideas ?
Let me see if I got it right (so I can try to help you).
You wanna represent sales (therefore the
Sales table). There can also exist discounts related to either a specific sale or a specific customer (therefore the
And your concern is that the
Transaction value column of the
Sales table won’t reflect the discounts made (if any)?
(Please tell me if I got it wrong)
Hi there @yizuhi. Thanks for the reply.
I think you’re correct, In the Sales table the Transaction value will not reflect the discount, so it asks to how to calculate the Transaction value based on the Discount table by joining them and maybe displaying as new column.
You could try something like this:
(sales.transaction_value * discounts.discount_value) AS transaction_value_with_discount
ON sales.sales_order_id = discounts.sales_order_id
Discount Value values will be like 0.5, 0.2, 0.8, etc. If not, you just have to fix that calculation I made there.)
This, however, will not alter the original table (no new columns are actually added to it), the results are only calculated for the query.
We are doing an inner join of the
Discounts tables, then selecting the
Sales Order Id of each sale, its
Transaction Value, and then calculating the
Transaction Value with Discount for each of the rows.
Thanks @yizuhi and @lisalisaj for the support