Customer - Order - Products Grouping question

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Customer - Order - Products Grouping question

Author

Topic

go_marquette
Starting Member

3 Posts

Posted - 2010-03-17 : 14:06:46

Hello - new to the forums, hoping someone can help me out.

I have 3 tables involved in this query:
- Customer
- Order
- Order Lines

I need a query that selects the total number of orders for each product category by customer, and also returns the total number of distinct orders for that customer.

For example...
I have Customer1 has 3 orders
Order1
- Product1
- Product2
- Product3
Order2
- Product1
Order3
- Product1
- Product2

The query should group by customer by product type. So, I should see a total order quantity of 3 for the customer, total of 3 for Product1 (since it appears in all three orders), 2 for Product2 (since it appears in 2 orders) and 1 for Product3 (since it appears in only 1 order).

I have the SQL written to return back the counts by product - but I can't figure out how to return back the number of total quotes by customer...thoughts? need more explanation?

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-17 : 14:12:27

[code]SELECT c.CustomerName,ProductName,
COUNT(o.OrderID) AS ProductOrderCount,
COUNT(o.OrderID) OVER (PARTITION BY c.CustomerName) AS OrderCount
FROM Customer c
JOIN Order o
ON o.CustomerID=c.CustomerID
JOIN OrderItems oi
ON oi.OrderID=o.OrderID
GROUP BY CustomerName,ProductName
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

go_marquette
Starting Member

3 Posts

Posted - 2010-03-17 : 14:18:45

Sorry - also forgot to mention that I need this data grouped by month, too, and that there's a 4th table (quotes). Quotes turn into orders - so really it's a pseudo-copy of the order table. Here's the SQL I have so far...

SELECT SoldToCustomer, pdct_desc,
MAX([1]) AS JanQuotes,
MAX([100]) AS JanOrders,
MAX([2]) AS FebQuotes,
MAX([200]) AS FebOrders,
MAX([3]) AS MarQuotes,
MAX([300]) AS MarOrders,
MAX([4]) AS AprQuotes,
MAX([400]) AS AprOrders,
MAX([5]) AS MayQuotes,
MAX([500]) AS MayOrders,
MAX([6]) AS JunQuotes,
MAX([600]) AS JunOrders,
MAX([7]) AS JulQuotes,
MAX([700]) AS JulOrders,
MAX(

) AS AugQuotes,
MAX([800]) AS AugOrders,
MAX([9]) AS SeptQuotes,
MAX([900]) AS SeptOrders,
MAX([10]) AS OctQuotes,
MAX([1000]) AS OctOrders,
MAX([11]) AS NovQuotes,
MAX([1100]) AS NovOrders,
MAX([12]) AS DecQuotes,
MAX([1200]) AS DecOrders,
(IsNull(MAX([1]), 0) + IsNull(MAX([2]), 0) + IsNull(MAX([3]), 0) +
IsNull(MAX([4]), 0) + IsNull(MAX([5]), 0) + IsNull(MAX([6]), 0) +
IsNull(MAX([7]), 0) + IsNull(MAX(

), 0) + IsNull(MAX([9]), 0) +
IsNull(MAX([10]), 0) + IsNull(MAX([11]), 0) + IsNull(MAX([12]), 0)) as YtdQuotes,
(IsNull(MAX([100]), 0) + IsNull(MAX([200]), 0) + IsNull(MAX([300]), 0) +
IsNull(MAX([400]), 0) + IsNull(MAX([500]), 0) + IsNull(MAX([600]), 0) +
IsNull(MAX([700]), 0) + IsNull(MAX([800]), 0) + IsNull(MAX([900]), 0) +
IsNull(MAX([1000]), 0) + IsNull(MAX([1100]), 0) + IsNull(MAX([1200]), 0)) as YtdOrders
FROM (
select SoldToCustomer, pdct_desc, MONTH(p.crt_ts) as mo1, MONTH(p.crt_ts) * 100 as mo2,
count(p.quote_id) as quotes, count(po_id) as orders
from quote p
INNER JOIN order_line on p.quote_id = l.quote_id and p.quote_rev = l.quote_rev
LEFT OUTER JOIN order o on p.quote_id = o.quote_number and p.quote_rev = o.quote_revision
WHERE pdct_desc is not null
group by SoldToCustomer, pdct_desc, month(p.crt_ts)
) AS T
PIVOT (
SUM(quotes) FOR [mo1] IN ([1], [2], [3], [4], [5], [6], [7],

, [9], [10], [11], [12])
) AS P1
PIVOT (
SUM(orders) FOR [mo2] IN ([100], [200], [300], [400], [500], [600], [700], [800], [900], [1000], [1100], [1200])
) AS P2
GROUP BY SoldToCustomer, pdct_desc

You'll see I can pull the quotes and orders back by product line by month, but I can't sum the total number of distinct quotes and orders for a customer by month.

go_marquette
Starting Member

3 Posts

Posted - 2010-03-17 : 16:31:02

When I run this, I get:
"Column 'o.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

quote:
Originally posted by visakh16

SELECT c.CustomerName,ProductName,
COUNT(o.OrderID) AS ProductOrderCount,
COUNT(o.OrderID) OVER (PARTITION BY c.CustomerName) AS OrderCount
FROM Customer c
JOIN Order o
ON o.CustomerID=c.CustomerID
JOIN OrderItems oi
ON oi.OrderID=o.OrderID
GROUP BY CustomerName,ProductName

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources