sql server - Display row and count of referenced row in SQL select statement -

sql server - Display row and count of referenced row in SQL select statement -

i have 2 tables. follows

table: category categoryid categoryname ---------- ------------- 1 fruit 2 country 3 auto table: items itemid categoryid itemname ------- ---------- ---------- 1 1 apple 2 1 mango 3 1 banana 4 2 usa 5 2 state of japan 6 3 honda 7 3 toyota

i need select query give me categories, along count of items under each category. this:

categoryid categoryname itemcount ---------- ------------- ---------- 1 fruit 3 2 country 2 3 auto 2

how can accomplish in sql query? need single query, not procedure variables :(

select c.categoryid, c.categoryname, count(*) itemcount category c inner bring together items on i.categoryid = c.categoryid grouping c.categoryid, c.categoryname

sql sql-server