getting summary in a stored procedure

smiter · April 24th, 2007, 05:41 AM

Hi, I am busy with a stored procedure for a group press database.

I have 2 tables: tbl_groups and tbl_articles

tbl_groups (intID, strGroupName)
tbl_articles (intID, intGroupID, txtArticle)

It is no problem to get the groups with articles but I also need the summary of articles for each group seperate.

This is what I have now:

Code:

ALTER PROCEDURE lbl_brands_lst    
(
@intGroupTypeID int = 0
)

AS
    SET NOCOUNT ON 

    SELECT        

                1                AS tag,
            null                AS parent,    
            g.intID                AS [Brand!1!ID!element],    
            g.strGroupName        AS [Brand!1!Name!element],
            g.intGroupTypeID    AS [Brand!1!GrouptypeID!element],
    (SELECT count(a.intID) FROM tbl_Articles a 
    INNER JOIN tbls_Groups g ON a.intGroupID = g.intID) AS [Brand!1!Summary!element]


            FROM         tbls_Groups g
            INNER JOIN tbl_Articles a ON a.intGroupID = g.intID

            WHERE g.intGroupTypeID = @intGroupTypeID            

            ORDER BY g.strGroupName ASC
            FOR XML EXPLICIT

But the summary node gives the total result of all articles and not per group. How is it possible to filter this in the "SELECT COUNT.." part of the stored procedur.

Thanks already!

Smiter

rstelma · April 24th, 2007, 05:37 PM

Its just a wild shot but maybe by using GROUP BY in the outer query.