I'm new to SQL Server and I'm trying to do some exercises. I want to find customers who bought least on W3schools database. My solution for this case is:

1. Join Customers with OrderDetails via CustomerID
2. Select CustomerNames that have least OrderID appeared after using JOIN.

Here is my query:

SELECT COUNT(OrderID), CustomerID
FROM Orders
GROUP BY CustomerID
ORDER BY COUNT(CustomerID) ASC
HAVING COUNT(OrderID) = '1'

When I ran this query, message says "Syntax error near "Having". What happened with my query? Please help me to figure out.

CodePudding user response：

My solution for this case is:

1. Join Customers with OrderDetails via CustomerID
2. Select CustomerNames that have least OrderID appeared after using JOIN.

As @thorsten-kettner lamented:

You say in your explanation that you join and then show the customer name. Your query does neither of the two things...

Furthermore, your question has severe grammatical errors making it hard to decipher.

I want to find customers who bought least on W3schools database.

Nonetheless,

The Try-SQL Editor at w3schools.com

1. To get the list of customers who have at least 1 order:

SELECT C.CustomerName FROM [Customers] AS C
JOIN [Orders] AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
ORDER BY C.CustomerName

2. To get the list of customers who have exactly 1 order:

SELECT C.CustomerName FROM [Customers] AS C
JOIN [Orders] AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
HAVING COUNT(O.OrderID) = 1
ORDER BY C.CustomerName

3. To get the customer who made the least number of orders:

Including the ones who made no order. Use JOIN instead of LEFT JOIN if you only want to consider the ones who made at least one order.

You can remove LIMIT 1 to get the whole list sorted by the number of orders placed.

SELECT C.CustomerName, COUNT(O.OrderID) FROM [Customers] AS C
LEFT JOIN [Orders] AS O
ON C.CustomerID = O.CustomerID
GROUP BY C.CustomerID
ORDER BY COUNT(O.OrderID), C.CustomerName
LIMIT 1;

Addendum

As commented by @sticky-bit ,

The ORDER BY clause has to come after the HAVING clause.

CodePudding user response：

You want a TOP 1 WITH TIES query, something like this:

SELECT TOP 1 WITH TIES CustomerID
FROM Orders
GROUP BY CustomerID
ORDER BY COUNT(OrderID);

In case you are using MySQL, try the following version:

SELECT CustomerID
FROM Orders
GROUP BY CustomerID
HAVING COUNT(OrderID) = (
    SELECT COUNT(OrderID)
    FROM ORDERS
    GROUP BY CustomerID
    ORDER BY COUNT(OrderID)
    LIMIT 1
);