CODEWITHSUNDEEP
sqloracle-database
nic.tavares22
nic.tavares22

Reputation: 11

How to select CustomerName and average ProductPrice

For a homework problem I have these tables are provided:

Product
-ProductID(Key)
-ProductName
-ProductPrice
-VendorID
-CategoryID

SoldVia
-ProductID(Key)
-TID(FK)
-NoOfItems

SalesTransaction
-TID(Key)
-TDate
-CustomerID(FK)
-StoreID(FK)

Customer
-CustomerID(Key)
-CustomerName
-CustomerZip

And I am supposed to select the CustomerName and average price for each customer. I am confused as to how to only average the productprices of the productID's that a specific customer bought.

I tried this:

SELECT Customer.CustomerName, AVG(Product.ProductPrice)
FROM Customer, Product
WHERE Customer.CustomerID = (SELECT CustomerID
                            FROM SalesTransaction
                            WHERE TID = (SELECT TID 
                                        FROM SoldVia
                                        WHERE ProductID = (SELECT ProductID
                                                          FROM Product)));

Upvotes: 1

Views: 229

Answers (1)

user5683823
user5683823

Reputation:

Probably something like this:

select c.customername, 
       sum(sv.noofitems  * p.productprice) / sum(sv.noofitems) as avg_price
from   customer c join salestransaction st on  c.customerid = st.customerid
                  join soldvia          sv on st.tid        = sv.tid
                  join product          p  on sv.productid  =  p.productid
group  by c.customerid
;

This will not return customers that bought nothing at all. If you must include those customers too (with NULL for the average price - nothing else makes sense), then you will need to change all the join to left outer join.

Note that "average price" is a weighted average (weighted by the number of units - the quantity - purchased for each distinct product), and therefore it can't be computed with the standard aggregate function avg().

Upvotes: 1

Related Questions