SQL Server Count on a grouped by

I got 3 tables. I want to group by th开发者_运维百科e item.name and count the times it can be found in idetail. This query only counts all rows not the grouped by rows.

Anyone knows how to fix it?

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

This is what you want:

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

If you want all three columns and you are using 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  

Another simpler version if you are using 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