Get customers with x 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 2008 Forums

Transact-SQL (2008)

Get customers with x orders

Author

Topic

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-10-21 : 10:49:29

I hate SQL. How the hell do I get all customers with 2+ orders:

select * from customer c
inner join Orders o
on c.id = o.customerID
where COUNT(o.id) > 2
group by o.id, c.id

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-21 : 10:50:41

select * from customer c
inner join Orders o
on c.id = o.customerID
group by o.id, c.id
having COUNT(o.id) > 2

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-10-21 : 11:38:42

ì would suspect this won't work, because of the "select *". you need to have the fields listed in the group by clause, named along with an aggregate function in the select condition.

something more like below?

select * from customers a
where exists (select b.customerid, count(*) from orders b where b.customerid = a.id group by b.customerid having count(*) > 2)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-21 : 12:59:08

[code]select columns...
from
(select *,count(o.id) over (partition by c.id) as ordcnt from customer c
inner join Orders o
on c.id = o.customerID
)t
where ordcnt>2
[/code]

YellowBug
Aged Yak Warrior

616 Posts

Posted - 2009-10-21 : 16:19:37

True. Thanks for correcting me.

CoffeeAddict
Yak Posting Veteran

94 Posts

Posted - 2009-10-21 : 17:06:52

Thanks...I knew I had to be close.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources