Query help - 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

General SQL Server Forums

New to SQL Server Programming

Query help

Author

Topic

BStarbuck
Starting Member

4 Posts

Posted - 2009-05-08 : 17:29:29

Heres my deal....
The objective is to return any customer who has made more the X amount of orders.
The code I used to return any customers who have made any orders is

Select		fname,
			lname			
	From	        Customer
	Where           id 
	In	(Select Distinct Customer$id
		From Orders) 
Go

My question is, how do I return duplicate Customer$id from my Orders table?
Any help would be great, but a link or book reference would be awesome.

Skorch
Constraint Violating Yak Guru

300 Posts

Posted - 2009-05-08 : 19:18:08

Try this:

SELECT fname, lname
FROM Customer c
RIGHT JOIN Orders o ON c.id = o.Customer$id
GROUP BY fname, lname
HAVING COUNT(o.Customer$id) > X

Some days you're the dog, and some days you're the fire hydrant.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-09 : 04:30:23

quote:
Originally posted by BStarbuck

Heres my deal....
The objective is to return any customer who has made more the X amount of orders.
The code I used to return any customers who have made any orders is

Select fname,
lname
From Customer
Where id
In (Select Distinct Customer$id
From Orders)
Go

My question is, how do I return duplicate Customer$id from my Orders table?
Any help would be great, but a link or book reference would be awesome.

for returning customers who have done more than x number of orders, use below


SELECT c.*
FROM Customers c
INNER JOIN (SELECT CustomerID,COUNT(OrderID) AS OrderCnt
           FROM Orders
           GROUP BY CustomerID)o
ON o.CustomerID=c.CustomerID
WHERE o.OrderCnt > X

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources