Need help in making SQL query

Question

i'm very new to SQL and don't know what is wrong with my query. plz guide

I have 2 tables product and product_consumer 1 to m relation. i want to get all products and count of consumers of each product.

This is what i want:

id:    pCount
item1, 2
item2, 1

i have tried the following query but it only return 1st record/1 row only.

SELECT prd.*, COUNT(pc.id) pCount 
FROM `product` AS prd 
JOIN product_consumer pc ON pc.id = prd.id

Answer 1

Try this

SELECT pc.id, COUNT(pc.id) pCount 
FROM product AS prd 
JOIN product_consumer pc ON pc.id = prd.id 
GROUP BY pc.id

Let's say your schema is this:

CREATE TABLE product(
  id int,
  product  varchar(1)
)

CREATE TABLE product_consumer(
  id int,
  consumer  varchar(1),
  pid int
)

if you want the details of all products with the count you need to include all the attributes of prd in group by clause like this :

SELECT prd.id, prd.product, count(pc.pid) as pCount  
FROM product AS prd 
JOIN product_consumer pc ON pc.pid = prd.id 
GROUP BY prd.id, prd.product, pc.pid;

Refer this SQLFiddle

Answer 2

Actually if you do not need the product name you do not need to join

SELECT id, count(*)
FROM product_consumer
GROUP BY id;

Answer 3

Use the GROUP BY Clause

SELECT prd.id, count(pc.id) pCount 
FROM product AS prd JOIN product_consumer pc ON pc.id = prd.id
GROUP BY prd.id