select only records with more than 1 record

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)

select only records with more than 1 record

Author

Topic

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-02-12 : 16:53:43

SELECT COUNT(CustomerID) AS exp1, CustomerID
FROM Orders
GROUP BY CustomerID
HAVING (exp1 > 1)

I get "invalid column exp1"

I want to return the customerID and a count of there orders... i dont know why i cant figure it out.. Errr

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 16:55:24

quote:
Originally posted by cwfontan

SELECT COUNT(Orders) AS exp1, CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(Orders)>1

I get "invalid column exp1"

I want to return the customerID and a count of there orders... i dont know why i cant figure it out.. Errr

cwfontan
Yak Posting Veteran

87 Posts

Posted - 2009-02-12 : 16:57:47

Thanks,, i didnt think i could put aggregate under where.. BUT ITS not.. the having allows it. wow.

quote:
Originally posted by sodeep

quote:
Originally posted by cwfontan

SELECT COUNT(Orders) AS exp1, CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(Orders)>1

I get "invalid column exp1"

I want to return the customerID and a count of there orders... i dont know why i cant figure it out.. Errr

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-12 : 17:03:09

Welcome.

Select * from 
(Select distinct CustomerID, Count(Orders) over (Partition by CustomerID)as OrderCount
from Orders)Z
Where OrderCount>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources