Problem in sql query

Question

Hi

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

  SELECT Order.id
  FROM Order
  INNER JOIN orderDetails 
   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

help me i am new to sql

Answer 1

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

Answer 2

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

Answer 3

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.

Answer 4

Use Group by

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