CODEWITHSUNDEEP
sqlsql-serverstored-procedures
user2571510
user2571510

Reputation: 11377

SQL Server: Select and count in one query

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.

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.

Expected output:

Thanks for any help with this, Tim.

Upvotes: 1

Views: 1477

Answers (2)

Sameer
Sameer

Reputation: 3183

  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.

Upvotes: 1

Moho
Moho

Reputation: 16498

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

Upvotes: 2

Related Questions