Why is unit_price used instead of total?

Screen Link: https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/5/analyzing-sales-by-country

Given solution: https://github.com/dataquestio/solutions/blob/master/Mission191Solutions.ipynb

The provided solution uses unit_price instead of using total. The average sales per customer and average order value will vary only if a customer has multiple orders with different invoice_id. If total is used, both of these values are same.

I need help figuring out what is the reason behind the difference in the two results (total, unit_price) and why has unit_price been taken into consideration.

Thanks in advance.

1 Like

hi @bhavya
Yes you are right taking sum of total make more sense to answer task given in mission, because total/sales = unit_price * quantity
but here in this dataset quantity is same for all the tracks in a particular invoice, so, total of unit_price of all tracks for particular invoice in invoice_line table and total in invoice is same for any particular invoice.
So whether you take unit_price or total for calculation you will get the same answer in both the cases when grouping by country, using the unit_price is pretty much convenient as far as code is concerned.

Hope this helps!

2 Likes

HI! @bhavya
If you are satisfied with the answer, it would be great if you could mark it as a solution, so it will help other community members while searching for the same question.

Thank you.
Happy learning!

Hi @harsh.raizada
unit_price and total are not the same as you say. If they were the results would be the same.
I have figured out the solution using total.

SELECT
       COUNT(DISTINCT(c.customer_id)) total_customers,
       COUNT(i.invoice_id) total_orders,
       ROUND(SUM(i.total),2) total_sales
FROM invoice i
INNER JOIN customer c ON c.customer_id = i.customer_id
GROUP BY 1;

avg_sales_per_customer = total_sales/total_customers
avg_order_value = total_sales/total_orders

4 Likes