title: SQL Server: Grouping items not working
body: <p>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. </p>
  
  <p>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. </p>
  
  <p>Can someone here help me with this ?</p>
  
  <p><strong>My stored procedure:</strong> </p>
  
  <pre><code>  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
  </code></pre>
  
  <p><strong>Current result:</strong> </p>
  
  <pre><code>&lt;ranks&gt;
    &lt;categoryX&gt;
      &lt;categoryX&gt;Category 1&lt;/categoryX&gt;
      &lt;itemID&gt;ID 1&lt;/itemID&gt;
      &lt;groupCount&gt;1&lt;/groupCount&gt;
      &lt;groupNewest&gt;2013-11-21&lt;/groupNewest&gt;
    &lt;/categoryX&gt;
    &lt;categoryX&gt;
      &lt;categoryX&gt;Category 2&lt;/categoryX&gt;
      &lt;itemID&gt;ID 2&lt;/itemID&gt;
      &lt;groupCount&gt;1&lt;/groupCount&gt;
      &lt;groupNewest&gt;2013-10-30&lt;/groupNewest&gt;
    &lt;/categoryX&gt;
    // ...
  &lt;/ranks&gt;
  </code></pre>
  
  <p><strong>Expected result:</strong> </p>
  
  <pre><code>&lt;ranks&gt;
    &lt;categoryX&gt;
      &lt;categoryX&gt;Category 1&lt;/categoryX&gt;
      &lt;groupCount&gt;3&lt;/groupCount&gt;
      &lt;groupNewest&gt;2013-11-21&lt;/groupNewest&gt;
        &lt;itemID&gt;ID 1&lt;/itemID&gt;
        &lt;itemID&gt;ID 2&lt;/itemID&gt;
        &lt;itemID&gt;ID 3&lt;/itemID&gt;
      &lt;categoryX&gt;Category 2&lt;/categoryX&gt;
      &lt;groupCount&gt;4&lt;/groupCount&gt;
      &lt;groupNewest&gt;2013-10-15&lt;/groupNewest&gt;
        &lt;itemID&gt;ID 1&lt;/itemID&gt;
        &lt;itemID&gt;ID 2&lt;/itemID&gt;
        &lt;itemID&gt;ID 3&lt;/itemID&gt;
        &lt;itemID&gt;ID 4&lt;/itemID&gt;
      // ...
    &lt;/categoryX&gt;
  &lt;/ranks&gt;
  </code></pre>
  
  <p>Many thanks for any help with this, Tim.</p>