Link to home
Start Free TrialLog in
Avatar of John500
John500Flag for United States of America

asked on

Help with query against AdventureWorks database - Average Sale Price

Need a hand with this query:

Return the average Sale Price by Product Category

Thanks!
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

I notice you have a number of these questions, so I will show you this one in generic terms to ensure that you get the understanding of what to do here.


Your sales table has:

SalesPrice
ProductId

Your product table has:

ProductId
CategoryId

Your category table has:

CategoryId
CategoryName

What you will need to do is figure out whether you want ALL product categories regardless of having parts assigned and/or having sales.  If you want to have all categories you would use a LEFT JOIN, if only those with sales (INNER) JOIN.

For the product and sales data we will use a JOIN for both.  Let's start with that:

   select p.categoryid, avg(s.salesprice) as avgsalesprice
   product p
   inner join sales s on p.productid = s.productid
   group by p.categoryid

What this gives you is your average sales by category id.

Now we apply our category table to it:

select c.categoryname
     , coalesce(sls.avgsalesprice, 0.00) as avgsalesprice
from category c
left join (
   select p.categoryid, avg(s.salesprice) as avgsalesprice
   product p
   inner join sales s on p.productid = s.productid
   group by p.categoryid
) sls on c.categoryId = sls.categoryId
;

What we have done here is to put our previous query as a derived table with alias "sls" and then joined on the categoryid.  Now we can display a category name and its average sales price or 0 using coalesce to filter nulls or no sales.

Hope that helps.

Kevin
Avatar of John500

ASKER

Kevin,

Yep, that helps.  Appreicate that.

Out of curiosity, do you feel you have the latest version of the database:

http://msftdbprodsamples.codeplex.com/releases/view/4004

Thanks
My code above was pseudo code and not meant to be the actual structure from the codeplex. As stated above, I realized after answering a couple that it would be better to teach you the concept than write code for you. Take the information from above and try your hand at the code against the AdventureWorks tables yourself.

If you have difficulty, then post back here the query you have constructed and what issues you are having with it.
ASKER CERTIFIED SOLUTION
Avatar of Mark Wills
Mark Wills
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial