CODEWITHSUNDEEP
sql
randomguy
randomguy

Reputation: 12242

How to get products and the number of buys each has with one SQL query?

Buy has a foreign key product_id.

So in addition to SELECT * FROM products; we are interested in getting the number of buys each product has.

Upvotes: 0

Views: 96

Answers (3)

Jamie Howarth
Jamie Howarth

Reputation: 3323

Something along the lines of:

SELECT p.*, COUNT(buy.product_id) FROM product p INNER JOIN buy ON buy.product_id = p.id GROUP BY buy.product_id

should do the trick.

Upvotes: 3

JClaspill
JClaspill

Reputation: 1745

SELECT p.*,COUNT(b.product_id) FROM products p
INNER JOIN buy b ON p.id=b.product_id
GROUP BY b.product_id

Thats about all I can offer with the info you've posted. Table structure of both would help a ton if the above doesn't work.

Upvotes: 1

Dustin Laine
Dustin Laine

Reputation: 38503

Join

SELECT Products.*, COUNT(Buy.ID) AS Bought
FROM products
    LEFT OUTER JOIN Buy ON products.ID = Buy.product_id
GROUP BY Buy.product_id

Subquery

SELECT 
    Products.*, 
    (SELECT COUNT(ID) FROM Buy WHERE product_id = Products.ID) AS Bought
FROM products

Upvotes: 1

Related Questions