SQL Server Cuente con un agrupado por

Tengo 3 tables. Quiero agrupar por item.name y contar las veces que se puede encontrar en idetail. Esta consulta solo count todas las filas, no las agrupadas por filas.

Alguien sabe como arreglarlo?

SELECT i.name, COUNT(i.name) FROM item AS i INNER JOIN item_category AS ic ON i.i_category_id = ic.ic_id INNER JOIN idetail AS id ON ic.ic_id = id.id_category_id WHERE ic.ic_id = 1002 GROUP BY i.name 

Esto es lo que quieres:

 select x.name , count(*) as cntNames , sum(x.cntDetails) as cntDetails from ( SELECT i.name, COUNT(*) as cntDetails FROM item AS i INNER JOIN item_category AS ic ON i.i_category_id = ic.ic_id INNER JOIN idetail AS id ON ic.ic_id = id.id_category_id WHERE ic.ic_id = 1002 -- NOTICE THE 2nd Value in the group by! GROUP BY i.name,id.id_category_id ) x group by name 
 Select Count(*) From ( Select i.name, Count(i.name) From item As I Join item_category As ic On ic.ic_id = i.category_id Join idetail as ID On ID.id_category_id = ic.ic_id Where ic.ic_id = 1002 Group By i.name ) As Z 

Si desea las tres columnas y está usando SQL Server 2005+

 With Items As ( Select I.name, Count(i.name) As ItemCount From item As I Join item_category As ic On ic.ic_id = i.category_id Join idetail as ID On ID.id_category_id = ic.ic_id Where ic.ic_id = 1002 Group By i.name ) Select Name, ItemCount , ( Select Count(*) From Items ) As OverallCount From Items 

Otra versión más simple si está usando SQL Server 2005+.

 Select i.name , Count(i.name) As CountByName , Count(*) Over() As TotalCount From item As I Join item_category As ic On ic.ic_id = i.category_id Join idetail as ID On ID.id_category_id = ic.ic_id Where ic.ic_id = 1002 Group By i.name