Counting the Many

Recently in an interview I was asked to provide a query for the following tables:

ORDER
orderID
orderDate

ORDERITEM
orderItemID
orderID
OrderDescription

These tables have a one-to-many relationship, as in each order can have multiple order items. The objective of the SQL statement was to return all the orders with a count of how many order items each order had. My first solution involved using a join:

SELECT Orders.OrderID, Orders.OrderDate, COUNT(OrderItems.OrderItemID) AS NoOfItems
FROM Orders LEFT JOIN OrderItems on Orders.OrderID = OrderItems.OrderID
GROUP BY Orders.OrderID, Orders.OrderDate

This achieved the objective but it was pointed out that I could have just used a subquery. Using a subquery is simpler as well as avoids some of the possible issues using JOINs:

SELECT Orders.OrderId,
       Orders.OrderDate,
       (SELECT COUNT(*)
        FROM OrderItems
        WHERE Orders.OrderID = OrderItems.OrderID) AS NoOfItems
FROM Orders

Ta-da! Two ways to get a count of items from the ‘many’ table in a one-to-many relationship. Pretty simple stuff but posted here for your convenience.