SQL Server: Grouping items not working

Question

I am new to SQL and need to create a stored procedure that fetches all items from a table + groups them by category and adds the count for each category.

So far I have the following which all the items correctly but does not group and count them anymore by category (see below). The issue here is that it lists all items separately instead of grouping them under the corresponding group. I assume I have to nest the select here but don't know how to do that.

Can someone here help me with this ?

My stored procedure:

  CREATE PROCEDURE [dbo].[FetchRequests]

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

Current result:

<ranks>
  <categoryX>
    <categoryX>Category 1</categoryX>
    <itemID>ID 1</itemID>
    <groupCount>1</groupCount>
    <groupNewest>2013-11-21</groupNewest>
  </categoryX>
  <categoryX>
    <categoryX>Category 2</categoryX>
    <itemID>ID 2</itemID>
    <groupCount>1</groupCount>
    <groupNewest>2013-10-30</groupNewest>
  </categoryX>
  // ...
</ranks>

Expected result:

<ranks>
  <categoryX>
    <categoryX>Category 1</categoryX>
    <groupCount>3</groupCount>
    <groupNewest>2013-11-21</groupNewest>
      <itemID>ID 1</itemID>
      <itemID>ID 2</itemID>
      <itemID>ID 3</itemID>
    <categoryX>Category 2</categoryX>
    <groupCount>4</groupCount>
    <groupNewest>2013-10-15</groupNewest>
      <itemID>ID 1</itemID>
      <itemID>ID 2</itemID>
      <itemID>ID 3</itemID>
      <itemID>ID 4</itemID>
    // ...
  </categoryX>
</ranks>

Many thanks for any help with this, Tim.

Answer 1

select
    lr.categoryX,
    count(*) as groupCount,
    max(lr.dateX) as groupNewest,
    (
        select t.ItemID
        from LogRequests as t
        where t.logStatus = 'active' and t.categoryX = lr.categoryX
        for xml path(''), type
    )
from LogRequests as lr
where lr.logStatus = 'active'
group by lr.categoryX
for xml path('categoryX'), root('ranks')