CODEWITHSUNDEEP
sqlsql-server
Manas Saha
Manas Saha

Reputation: 1497

Display row and count of referenced row in SQL select statement

I have two tables. as follows

Table: Category

categoryID  categoryName
----------  -------------
    1         Fruit
    2         Country
    3         Car

Table: Items

 ItemID    CategoryID   ItemName
 -------   ----------   ----------
    1          1         Apple
    2          1         Mango
    3          1         Banana
    4          2         USA
    5          2         Japan
    6          3         Honda
    7          3         Toyota

I need a select query which will give me the categories, along with the count of items under each category. like this:

categoryID  categoryName    ItemCount
----------  -------------   ----------
    1         Fruit             3
    2         Country           2
    3         Car               2

How can I achieve this in a SQL query? I need a single query, not a procedure with variables :(

Upvotes: 0

Views: 324

Answers (2)

Meherzad
Meherzad

Reputation: 8563

Try this query

SELECT c.categoryID,  c.categoryName, count(*) as 'ItemCount' 
FROM Category c, Items i 
WHERE c.categoryID = i.categoryID 
GROUP BY c.categoryID,  c.categoryName;

Fiddle

Hope this helps

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60503

select c.categoryID, c.categoryName, count(*) as ItemCount
FROM category c
inner join items i on i.categoryId = c.categoryId
GROUP BY c.categoryID, c.categoryName

Upvotes: 2

Related Questions