CODEWITHSUNDEEP
sqlsql-server
user603007
user603007

Reputation: 11794

sql server query: how to select customers with more than 1 order

I am a sql server newbie and trying to select all the customers which have more than 1 orderid. The table looks as follows:

CREATE TABLE [dbo].[orders](
    [customerid] [int] NULL,
    [orderid] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (1, 2)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (1, 3)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (2, 4)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (2, 5)
INSERT [dbo].[orders] ([customerid], [orderid]) VALUES (3, 1)

Upvotes: 6

Views: 25532

Answers (2)

smknstd
smknstd

Reputation: 351

as you'll probably need customer data at some point, you could also try:

select *
from customers
where exists (
    select count(*)
    from    orders
    where customers.id = customerid
    group by customerid
    having  count(*) > 1
)

Upvotes: 4

Andomar
Andomar

Reputation: 238048

select  customerid
,       count(*) as order_count
from    orders
group by
        customerid
having  count(*) > 1

Upvotes: 16

Related Questions