I'm trying to select 1000 customers that have placed exactly 1 order. Everything is in the Orders table.
select * from
Orderhaving 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.
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
... FROM `Order` WHERE ... GROUP BY ... should work. See updated answer.WHERE is executed as a condition on the query (before aggregate functions), whereas HAVING is performed afterwards since it targets aggregated data (e.g., COUNT()).SELECT * FROM `Order`
GROUP BY CustomerID
HAVING COUNT(CustomerID) = 1
LIMIT 1000
try
select count(CustomerID) as counter ,o.* from Order o
group by CustomerID having counter = 1 limit 1000
Add group by to query for correct output, e.g:
select * from Order group by CustomerID having count(CustomerID) = 1 limit 1000
SELECT CustomerID FROM Order GROUP BY CustomerID HAVING COUNT(*) = 1 LIMIT 1000
