CODEWITHSUNDEEP
sqloracle-database
JBBeginner
JBBeginner

Reputation: 35

SQL Count with a Join

select product_name, product_order.Count(*)
From product Join product_order
ON product.product_id = product_order.product_id
where product.product_id = product_order.product_id;

I have been working on this query for an hour and can't seem to get it to work. All I need to do is take the product from one table match it to how many times it was ordered in another. Then display the product in one column and how many times it was ordered in the next.

Upvotes: 0

Views: 64

Answers (2)

David Aldridge
David Aldridge

Reputation: 52386

This should be as simple as:

  select product_name,
         count(product_order.product_id)
    From product left join product_order
      on product.product_id = product_order.product_id
group by product_name

I used a left join and counted on product_order.product_id so products that have not been ordered will still display, with a count of zero.

Upvotes: 1

Chrisrs2292
Chrisrs2292

Reputation: 1094

This is how I would do it:

select p.product_name,
       (select count(*) from product_order po where p.product_id = po.product_id) times_ordered
from   product p

Alternatively, you could use a group by statement:

select p.product_name,
       count(po.product_id)
from   product p,
       product_order po
where  p.product_id = po.product_id(+)
group by p.product_name

Upvotes: 0

Related Questions