Solved

Help with query against AdventureWorks database - Average Sale Price

Posted on 2010-08-28
5
920 Views
Last Modified: 2012-05-10
Need a hand with this query:

Return the average Sale Price by Product Category

Thanks!
0
Comment
Question by:John500
  • 2
5 Comments
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33551302
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
0
 

Author Comment

by:John500
ID: 33551361
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
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 33551369
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.
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 500 total points
ID: 33561297
Hi John500

Interesting series of questions...

You really need to be getting "inside" that Adventureworks database. For example, the "product" scenarios : http://msdn.microsoft.com/en-us/library/ms124670(v=SQL.90).aspx  (use the drop down at the top to select SQL2005 or SQL2008 etc) and the data dictionary : http://msdn.microsoft.com/en-US/library/ms124438(v=SQL.90).aspx

Your question is quite ambiguous, not sure if you need help with understanding the data links or the SQL involved to achieve an average. Then, not sure if it is the average unit sales price (ie after discounts) or the average sales value being qty * (unit price - unit discount )...

And given the warning above, we normally need a hint as to where your difficulties are and that way we can help educate and clear up any specific problems you are having.

Anyway, have a look at this sample query from Adventureworks - it should go close, but you will need to modify a bit to take into account the specific requirements of your question...

SELECT PC.Name AS Category, PSC.Name AS Subcategory, P.ProductNumber, P.Name AS Product, avg(isnull(UnitPrice,0.00) - isnull(UnitPriceDiscount,0.00)) as Average_Unit_SalesPrice
FROM Production.Product AS P
INNER JOIN Production.ProductSubcategory AS PSC ON PSC.ProductSubcategoryID = P.ProductSubcategoryID
INNER JOIN Production.ProductCategory AS PC ON PC.ProductCategoryID = PSC.ProductCategoryID
INNER JOIN Sales.SalesOrderDetail as SD ON SD.ProductID = P.ProductID
Group By PC.Name, PSC.Name, P.ProductNumber, P.Name
Order by 1,2,3,4

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Creating and Managing Databases with phpMyAdmin in cPanel.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now