Solved

Help with query against AdventureWorks database - Customers purchasing more than 2

Posted on 2010-08-28
8
1,021 Views
Last Modified: 2012-08-14
Need a hand with this query:

Return the average Sale Price for all customers who have purchased two or more products

Thanks!
0
Comment
Question by:John500
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33551231
Select AvgSalePrice = SUM(SumUnitSalePrice)/Sum(SumSaleQty)
From
(
select c.CustomerID, SUM(d.UnitPrice*d.OrderQty) as SumUnitSalePrice, SUM(d.OrderQty) as SumSaleQty
from SalesLT.Customer c
inner join SalesLT.SalesOrderHeader h on h.CustomerID=c.CustomerID
inner join SalesLT.SalesOrderDetail d on d.SalesOrderID=h.SalesOrderID
group by c.CustomerID
having COUNT(d.SalesOrderDetailID) > 1 -- 2 or more products
) X

Not sure how you define "average Sale Price", but here's one definition.
Weighted total sale price (unit * qty) divided by total qty.
0
 

Author Comment

by:John500
ID: 33551345
Appreciate the feed back here.

I'm wondering if we have different versions of the database.  Supposedly, the latest version can be obtained here:

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

For instance, I don't have the anything labled SalesLT.

If I change all the SalesLT's to Sales and run this, I get:

389.0395

How about you ?


0
 
LVL 4

Assisted Solution

by:samijsr
samijsr earned 200 total points
ID: 33552200
cyberkiwi: solution having one bugs that it also calculte if same cutomer Purchased same item more than one, where as requirement is two or more items

Select SC.CustomerID,Avg(SumSalaesPrice)as 'AvgSlaesPrice' from Sales.Customer SC
Inner Join Sales.SalesOrderHeader SS on SC.CustomerID=SS.CustomerID
Inner Join
(Select SD.SalesOrderID,sum(LineTotal)as 'SumSalaesPrice' from Sales.SalesOrderDetail SD
Inner Join
(
Select SalesOrderID,count(SalesOrderID)as 'cnt'
from
(Select distinct SalesOrderID,ProductID from Sales.SalesOrderDetail)T1
Group by SalesOrderID having count(SalesOrderID)>1)T2
On SD.SalesOrderID=T2.SalesOrderID
group by SD.SalesOrderID)T3
on T3.SalesOrderID=SS.SalesOrderID
Group By SC.CustomerID
order by 1
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 33552294
John,

Can you confirm the requirement?

> Return the average Sale Price for all customers who have purchased two or more products

1) purchased two or more products
Does that mean distinct productid?
Just quantity of more than 1 across all invoices?]
More than 1 invoice line across all invoices

2) average Sale Price for all customers
Is that one figure for a grand total average, the result is a single row, single column like the "389.0395" figure?
Is it for each customer, an average sale price?
Or do you actually want a per-product average, with each row in the output as one product?

3) average Sale Price, using sample of just 2 sales (1 sale of qty 10 x unit price $1 + 1 sale of 1 x $10)
Is that a weighted average = ( $20 / 11 )
or a simple average = ( $11 / 2 ) ?
0
 

Author Comment

by:John500
ID: 33553650
Hey guys !

Thanks for the help here.  In answer to your questions, the goal is to determine the average price each individual is purchasing so long as that person has purchased at least two items.  Thus, an average can be determined.

So then, cyberkiwi's 3rd scenario is what we are after:

3) average Sale Price, using sample of just 2 sales (1 sale of qty 10 x unit price $1 + 1 sale of 1 x $10)
The weighted average = ( $20 / 11 )

The results should show two fields, the customerID and the average price for *everything* they have purchased.

I'm not certain but it may be most accurate or helpful to use OVER(PARTITION BY CustomerID) AS 'AVG_AMT' for this - yes/no ?  So in other words, the first SELECT statement would look something like this:

SELECT CustomerID, AVG(A.Amount_Column) OVER(PARTITION BY CustomerID) AS 'AVG_AMT'
...
...

Your thoughts?

0
 
LVL 58

Accepted Solution

by:
cyberkiwi earned 300 total points
ID: 33553730
John,

That answers questions 2 and 3, but for 1, I am still a bit fuzzy.

select CustomerID, SumUnitSalePrice/SumSaleQty as [Average Sale Price]
from
(
select c.CustomerID, SUM(d.UnitPrice*d.OrderQty) as SumUnitSalePrice, SUM(d.OrderQty) as SumSaleQty
from Sales.Customer c
inner join Sales.SalesOrderHeader h on h.CustomerID=c.CustomerID
inner join Sales.SalesOrderDetail d on d.SalesOrderID=h.SalesOrderID
group by c.CustomerID
having COUNT(distinct d.ProductID) > 1 -- 2 or more products
) X

Tweak the having clause as required.  It will be one of

having COUNT(distinct d.ProductID) > 1
having SUM(d.OrderQty) > 1
having COUNT(d.SalesOrderDetailID) > 1
0
 

Author Comment

by:John500
ID: 33553900
cyberkiwi,

That looks like the ticket.  The 'SalesOrderDetailID > 1' looks to be the answer as I see it.  That is, counting ALL the order details would appear to catch the whole shi-bang.  I may be wrong.

This is why I posted all of these questions.  I'm not familar with the AdventureWorks database and I need to provide answers in a short time frame.

Anyway, thanks both of you for the help on this!
0
 

Author Comment

by:John500
ID: 33554251
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
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…

733 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