Repeated elements counting for Sql datatables queries

I have 2 tables: an Order table and an orderDetails table. I have written a inner join:

  SELECT Order.id
  FROM Order
  INNER JOIN orde开发者_如何学GorDetails 
   ON Order.id=orderDetails.id

I have got the output as:

   id
  100
  100
  100
  101
  101

From the above data, I want the count of each record output as:

  id  count
  100  3
  101  2

How would I do this?

Select OrderId , Count(*) as [Count]
from OrderDetials
Group By OrderId 

OrderId will be a foreing key column referencing Order.Id column of Order Table

If your orderDetails.id references Order.id column This will be the query.

Select id , Count(*) as [Count]
from OrderDetials
Group By id

SELECT o.id, COUNT(*)
  FROM Order         o
  JOIN orderDetails  od  ON o.id=od.id
 GROUP BY o.id

You need to use the COUNT aggregate and the GROUP BY clause.

SELECT Order.id, COUNT(DISTINCT orderDetails.id)
FROM Order 
INNER JOIN orderDetails ON Order.id=orderDetails.orderId 
GROUP BY Order.id

It also looks like you need o alter the join condition slightly.

Use Group by

SELECT Order.id, count(*) as Count
FROM Order
INNER JOIN orderDetails 
ON Order.id=orderDetails.id Group by Order.id