分组依据上的SQL Server计数

Question

我有三张桌子。我想按item.name分组，并计算在idetail中可以找到它的次数。此查询只计算所有行的数量，而不计算分组依据行的数量。

有人知道怎么修吗？

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

Answer 1

这就是你想要的：

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

Answer 2

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

如果您想要这三列，并且您正在使用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  

如果您使用的是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