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);
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