CODEWITHSUNDEEP
mysqlsqldatabasemysql5
bobo
bobo

Reputation: 8727

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 orders 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.

Upvotes: 1

Views: 66

Answers (4)

Chandu
Chandu

Reputation: 82903

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

Upvotes: 2

Clockwork-Muse
Clockwork-Muse

Reputation: 13056

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.

Upvotes: 1

Michael Berkowski
Michael Berkowski

Reputation: 270607

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.

Upvotes: 1

AJ.
AJ.

Reputation: 28174

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.

Upvotes: 1

Related Questions