Multiple Orders - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Multiple Orders

Author

Topic

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-09-02 : 15:05:49

I need to find out customers having more than one order for the same product

Xyz table
---------
Customer_Id
Order_ID
Prodcut_ID
Abc
Mnr

I can get information like
Select customer_Id,Product_Id, Count(Customer_ID) Cnt from Xyz
Group By customer_Id,Product_Id having Count(Customer_ID) >= 2
Order By Customer_Id

But I want to include rest of the columns and I don’t want to use any temp tables either

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-02 : 15:10:51

So which row do you want to show Abc and Mnr for? Let's say the customer has 2 orders and Abc equals 0 and Mnr equals 0 in the first one, but in the second one, Abc equals 1 and Mnr equals 1. Which one do you want to show?

Please post some data for us and the expected result set to make this clearer.

Tara

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-09-02 : 15:17:34

Actually ABC and mnr are columns. Let’s say table structure is

Customer_Id
Order_ID
Prodcut_ID
Prod_Name
Prod_Cost

Now I want to get list of the Customer_Id,Order_ID,Prodcut_ID,Prod_Name,Prod_Cost for the customers having more than one order for the same product_ID

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-02 : 15:21:18

This should work:



SELECT x.Customer_Id, x.Order_ID, x.Product_ID, x.Prod_Name, x.Prod_Cost
FROM Xyz x
INNER JOIN 
	(
		SELECT customer_Id, Product_Id, COUNT(Customer_ID) AS Cnt 
		FROM Xyz 
		GROUP BY customer_Id, Product_Id 
		HAVING COUNT(Customer_ID) >= 2 
	) t
ON x.Customer_Id = t.customer_Id AND x.Product_ID = t.Product_Id

Tara

Shastryv
Posting Yak Master

145 Posts

Posted - 2004-09-02 : 15:32:54

Wow that’s fast Tara. Works excellent

I came up to

Select *
from Xyz
where Customer_Id in (
Select Customer_Id
from Xyz
Group by Customer_ID,Product_ID
Having Count(Customer_ID) >= 2 )

But couldn’t figure our rest…I guess I need to upgrade my T-SQL Skill. But that thank for your reply

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources