CODEWITHSUNDEEP
mysql
stef
stef

Reputation: 27749

MySQL: Select records where COUNT

I'm trying to select 1000 customers that have placed exactly 1 order. Everything is in the Orders table.

select * from Order having count(CustomerID) = 1 limit 1000

So basically all records that have only one occurence of the CustomerID in the entire table. This returns an empty result set and there are 100,000s in the table.

Upvotes: 0

Views: 1844

Answers (5)

jensgram
jensgram

Reputation: 31508

You need to GROUP in order to (meaningfully) use the HAVING clause. The following works as expected:

SELECT
    *
FROM
    `Order`
GROUP BY
    CustomerID
HAVING
    COUNT(CustomerID) = 1
LIMIT 1000

UPDATE
Adding WHERE (see comments):

SELECT
    *
FROM
    `Order`
WHERE
    Language = 'EN'
GROUP BY
    CustomerID
HAVING
    COUNT(CustomerID) = 1
LIMIT 1000

Upvotes: 4

Ēriks Daliba
Ēriks Daliba

Reputation: 718

SELECT CustomerID FROM Order GROUP BY CustomerID HAVING COUNT(*) = 1 LIMIT 1000

Upvotes: 0

digor_ua
digor_ua

Reputation: 592

Add group by to query for correct output, e.g:

select * from Order group by CustomerID having count(CustomerID) = 1 limit 1000

Upvotes: 0

Haim Evgi
Haim Evgi

Reputation: 125496

try

select count(CustomerID) as counter ,o.* from Order o
group by CustomerID having  counter  = 1  limit 1000

Upvotes: 0

Shakti Singh
Shakti Singh

Reputation: 86396

SELECT * FROM `Order`
GROUP BY CustomerID 
HAVING COUNT(CustomerID) = 1 
LIMIT 1000

Upvotes: 1

Related Questions