Which customer has placed most orders. SQL query

Question

I'm trying to query my database for my class to find out which customer has placed the most orders. The table I'm searching is a three attribute table that has the customerID, orderID, and the placedDate.

The query I thought would work is:

select cid from placed order by sum(oid);

But I keep getting an error saying cid is "not a single-group group function" the oid is the primary key and is a foreign key that references another table. Is that what the issue is?

Answer 1

If you want to count the number of orders you should do a count instead of a SUM:

SELECT cid,COUNT(*) 
FROM placed
GROUP BY cid
ORDER BY COUNT(*) DESC

This will give you the list of customers and their respective number of orders, ordered by the number of orders descendent.

If you want just the customer with most orders, you have to limit the number of records to the first one. For that, you have to tell what DBMS you use, since it varies with the DBMS the way you limit the query to the first one (ex: mysql is LIMIT 1, sql-server is TOP 1):

In Oracle, you can do:

SELECT * FROM (
  SELECT cid,COUNT(*) 
  FROM placed
  GROUP BY cid
  ORDER BY COUNT(*) DESC
  ) a
WHERE rownum = 1