SQL Server：在一个查询中选择并计数

I am using the following stored procedure to group the items in a table by category and add a count and max value to each group

我使用以下存储过程按类别对表中的项目进行分组,并为每个组添加计数和最大值

This works fine so far. How can I achieve, that for each group I also get the containing items listed ? When I just add a column (e.g. itemID) to the select here I get an error.

这到目前为止工作正常。我怎样才能实现,对于每个组我也会得到列出的包含项目?当我只在这里选择一个列(例如itemID)时,我收到一个错误。

My stored procedure so far:

到目前为止我的存储过程:

      ALTER PROCEDURE [dbo].[FetchRequests]

      AS
      BEGIN
      SET NOCOUNT ON;
      SELECT              categoryX,
                          COUNT(*) AS groupCount,
                          MAX(dateX) AS groupNewest
      FROM                LogRequests
      WHERE               logStatus = 'active'
      GROUP BY            categoryX
      ORDER BY            groupCount desc, categoryX
      FOR XML PATH('categoryX'), ELEMENTS, TYPE, ROOT('ranks')
      END

The error I get when I just add "itemID" in the above Select: Msg 8120, Level 16, State 1, Procedure FetchRequests, Column 'LogRequests.itemID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

我在上面添加“itemID”时出现的错误选择:消息8120,级别16,状态1,过程FetchRequests,列'LogRequests.itemID'在选择列表中无效,因为它不包含在聚合函数中或GROUP BY子句。

Expected output:

Category 1
- groupCount
- groupNewest
- list of all itemIDs with category = 1

类别1 groupCount组类别= 1的所有itemID的最新列表

Category 2
- groupCount
- groupNewest
- list of all itemIDs with category = 2

类别2 groupCount组类别= 2的所有itemID的最新列表

Category 3
- groupCount
- groupNewest
- list of all itemIDs with category = 3

类别3 groupCount组类别= 3的所有itemID的最新列表

Thanks for any help with this, Tim.

蒂姆,谢谢你对此有任何帮助。

2 个解决方案

#1

any non-aggregated field in the select clause must appear in the group by clause

select子句中的任何非聚合字段都必须出现在group by子句中

#2

  ALTER PROCEDURE [dbo].[FetchRequests]

  AS
  BEGIN
  SET NOCOUNT ON;
  SELECT              itemid,categoryX,
                      COUNT(*) AS groupCount,
                      MAX(dateX) AS groupNewest
  FROM                LogRequests
  WHERE               logStatus = 'active'
  GROUP BY            itemid,categoryX
  ORDER BY            groupCount desc, categoryX
  FOR XML PATH('categoryX'), ELEMENTS, TYPE, ROOT('ranks')
  END

You need to add itemid to groupby clause.

您需要将itemid添加到groupby子句。

#1