分组的SQL Server计数 [英] SQL Server Count on a grouped by

问题描述

我有3张桌子。我想根据item.name进行分组，并计算在idetail中可以找到的时间。

I got 3 tables. I want to group by the 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

推荐答案

这是你想要的：

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

这篇关于分组的SQL Server计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！