John500
asked on
Help with query against AdventureWorks database - Customers purchasing more than 2
Need a hand with this query:
Return the average Sale Price for all customers who have purchased two or more products
Thanks!
Return the average Sale Price for all customers who have purchased two or more products
Thanks!
ASKER
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 ?
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 ?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 ) ?
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 ) ?
ASKER
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
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!
ASKER
By the way, here are other question I could use the input on if you guys aren't already looking at them
https://www.experts-exchange.com/questions/26436727/Help-with-query-against-AdventureWorks-database-Update-sales-price.html
https://www.experts-exchange.com/questions/26436719/Help-with-query-against-AdventureWorks-database-Average-Sale-Price.html
https://www.experts-exchange.com/questions/26436712/Help-with-query-against-AdventureWorks-database-Customers.html
https://www.experts-exchange.com/questions/26436723/Help-with-query-against-AdventureWorks-database-Delete-Customers.html
https://www.experts-exchange.com/questions/26436709/Help-with-query-against-AdventureWorks-database-Products-ordered.html
https://www.experts-exchange.com/questions/26436727/Help-with-query-against-AdventureWorks-database-Update-sales-price.html
https://www.experts-exchange.com/questions/26436719/Help-with-query-against-AdventureWorks-database-Average-Sale-Price.html
https://www.experts-exchange.com/questions/26436712/Help-with-query-against-AdventureWorks-database-Customers.html
https://www.experts-exchange.com/questions/26436723/Help-with-query-against-AdventureWorks-database-Delete-Customers.html
https://www.experts-exchange.com/questions/26436709/Help-with-query-against-AdventureWorks-database-Products-ordered.html
From
(
select c.CustomerID, SUM(d.UnitPrice*d.OrderQty
from SalesLT.Customer c
inner join SalesLT.SalesOrderHeader h on h.CustomerID=c.CustomerID
inner join SalesLT.SalesOrderDetail d on d.SalesOrderID=h.SalesOrde
group by c.CustomerID
having COUNT(d.SalesOrderDetailID
) X
Not sure how you define "average Sale Price", but here's one definition.
Weighted total sale price (unit * qty) divided by total qty.