Building a SQL to calculate the number of rows relating to the parent

I am finding it hard to build a SQL, on the following database schema,

Shop

id

name

Order

id

shop_id

date

amount

You can see that a shop can have many orders. I would like to a build a SQL that gives the number of 开发者_如何学Corders of each shop on a specific date. So the WHERE part must be (e.g. 2011-7-13),

WHERE order.date = '2011-7-13'

I can't think of how the SELECT part should be like. I think COUNT(*) should be used but this is all what I have got in my brain now.

Could somebody please give some hints on this?

Many thanks to you all.

---

Try this:

SELECT a.id, a.name, COUNT(1) AS order_count
  FROM Shop a INNER JOIN Order b
    ON a.id = b.shop_id
 WHERE `date` = '2011-7-13'
 GROUP BY a.id, a.name

---

SELECT Shop.name, COUNT(*) AS Num_Orders
FROM Shop JOIN `Order` ON Shop.id = `Order`.id
WHERE DATE(`Order`.`date`) = '2011-7-13'
GROUP BY `Order`.id;

Note, in case date is a DATETIME column I used the DATE() function to return only the date part of it, not the time.

You'll probably have to quote date and Order in backquotes as above, since they're reserved MySQL keywords. Maybe not date but almost certainly Order.

---

try this:

SELECT COUNT(Order.id), Shop.name, Order.date
FROM Shop INNER JOIN Order ON Shop.id=Order.shop_id
WHERE Order.date = '$date'
GROUP BY Shop.name, Order.date
ORDER BY Order.date

I've included date in the results for verification.

---

After some consideration (and @AJ's well deserved remark), here's my (revised) answer.

SELECT shop.id, shop.name,
FROM shop
JOIN (SELECT shop_id, date, COUNT(*)
      FROM order
      WHERE date = :date
      GROUP BY shop_id, date) as order_count
ON order_count.shop_id = shop.id

If you want a list of all order counts by date, remove the inner WHERE clause.