how do i write the query for this table for the following resulting table?

I believe a COUNT(distinct a.AFGroupID) and COUNT(a.AFID) would make it work. See code below:

SELECT s.AFSetID, s.Tag, s.Name, s.ModifiedOn, 
COUNT(distint a.AFGroupID), COUNT(a.AFID)
    FROM  dbo.TAvoidFavorSet s with (nolock, forceseek)
    INNER JOIN @owners o ON o.OwnerId = s.OwnerID
    LEFT OUTER JOIN TAvoidFavor a WITH(NOLOCK) ON a.AFSetID = s.AFSetID AND ((@primaryOnly = 1 AND (a.GridLevel = 0 OR a.PrimaryAFID IS NULL)) OR (@primaryOnly = 0))
    LEFT OUTER JOIN Lookup_AvoidFavorType t ON t.TypeID = a.TypeID   
    LEFT OUTER JOIN dbo.TAvoidFavorPLID p WITH(NOLOCK) ON p.AFID = a.AFID
    WHERE s.AFSetID = COALESCE(@afSetID, s.AFSetID)
    GROUP BY s.AFSetID, s.ModifiedOn, s.Tag, s.Name

Since you want to count the AFID and AFGroupID, you do not want to group on them.

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top