Skip to content
Advertisement

Get count and grouping of foreign key

I have this product_user table which has foreign key product in it.

enter image description here

I need to group and count all of the products from that table with the same IDs and get that product name so result of the query could be:

+------------+--------------+-------+
| product id | product name | count |
+------------+----------------------+
| 36         | product 1    |   5   |       
+------------+----------------------+
| 22         | product 2    |   8   | 
+------------+--------------+-------+

I think I could use join table and one of many tries was:

select products.id - count(products.id)
from products
LEFT OUTER JOIN product_user ON product_user.product = products.id
group by products.Id

I am new at writing SQL queries so any hint can be useful.

Advertisement

Answer

This:

select products.id - count(products.id)

Should be:

select products.id, products.name, count(product_user.id)

Optionally change this:

group by products.id

To this:

group by products.id, products.name
User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement