Tudor Andrei Tudor Andrei - 24 days ago 18
SQL Question

SQL number of products

I have two tabels :

--Products--
idProduct PK
Category FK
Amount
Name

--Category--
idCategory PK
Category Name


if i have 20 products of different categories for example 3 cars 5 planes 9 types of food
how do i get all of them in a new joined table like this

Category Name (from --Category--) Amount of every product (from --Products--)
Cars 3
Planes 9


And so on , i don't have a table with all the elements from each category so what should i do ?

Answer

You may looking for simple join with aggregate function

SELECT CategoryName,COUNT(p.Category ) AS [Amount of every product ]
FROM Category c
INNER JOIN Product p ON c.idCategory = p.Category 
GROUP BY CategoryName
Comments