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.