CODEWITHSUNDEEP
sqlsql-serversql-server-2008selectgroup-by
user2571510
user2571510

Reputation: 11377

How to use Group By in certain Select in SQL Server?

I haven't used Group By that much before and always get an error when trying to group the following by the UserID

Error:

Column 'Log.Version' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

How can I avoid this ? What I am trying to get is a count for each user with every user only appearing once for each tool instead of listing every usage separately.

My query (working except for the above + simplified for demo):

ALTER PROCEDURE [dbo].Reporting_FetchUsage
AS
BEGIN
    SET NOCOUNT ON;

    SELECT      
        A.UserID, A.Tool, A.[Version], A.Note, A.[TimeStamp],
        B.ADesc, B.SDesc, B.DDesc, B.ALN
    FROM 
        Log A
    LEFT JOIN   
        EmployeeTable B ON B.NTID = A.UserID
    WHERE       
        A.[TimeStamp] > DATEADD(YEAR, -1, GETDATE())
        AND (A.Tool LIKE 'abc%'
             OR
             A.Tool LIKE 'def%'
             OR
             A.Tool LIKE 'ghi%')
        AND (B.DDesc = 'CB'
             OR
             B.DDesc = 'PS')
    GROUP BY    
        A.Tool, A.UserID
    ORDER BY    
        A.Tool, A.UserID
    FOR XML PATH('reporting'), ELEMENTS, TYPE, ROOT('ranks')
END

Upvotes: 0

Views: 81

Answers (2)

Matt
Matt

Reputation: 15061

Include all the fields in your GROUP BY clause that are in your SELECT statement that are not being used in an aggregate.

ALTER PROCEDURE [dbo].Reporting_FetchUsage

AS
BEGIN
    SET NOCOUNT ON;

    SELECT      A.UserID,
                A.Tool,
                A.[Version],
                A.Note,
                A.[TimeStamp],
                B.ADesc,
                B.SDesc,
                B.DDesc,
                B.ALN
    FROM        Log A
    LEFT JOIN   EmployeeTable B
    ON          B.NTID = A.UserID
    WHERE       A.[TimeStamp] > DATEADD(YEAR, -1, GETDATE())
    AND         (
                    A.Tool LIKE 'abc%'
                    OR
                    A.Tool LIKE 'def%'
                    OR
                    A.Tool LIKE 'ghi%'
                )
    AND         (
                    B.DDesc = 'CB'
                    OR
                    B.DDesc = 'PS'
                )
GROUP BY    A.Tool, 
            A.UserID, 
            A.[Version],
            A.Note,
            A.[TimeStamp],
            B.ADesc,
            B.SDesc,
            B.DDesc,
            B.ALN
    ORDER BY    A.Tool, A.UserID
    FOR XML PATH('reporting'), ELEMENTS, TYPE, ROOT('ranks')

END

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269445

What I am trying to get is a count for each user with every user only appearing once for each tool instead of listing every usage separately.

If this is the case, I would expect to see a count() somewhere. Perhaps this is closer to what you want:

SELECT l.UserID, l.Tool, e.ADesc, e.SDesc, e.DDesc, e.ALN,
       COUNT(*) as NumTools
FROM Log l LEFT JOIN 
     EmployeeTable e
     ON e.NTID = l.UserID
WHERE l.[TimeStamp] > DATEADD(YEAR, -1, GETDATE()) AND
      (l.Tool LIKE 'abc%' OR l.Tool LIKE 'def%' or l.Tool LIKE 'ghi%') AND
      B.DDesc IN ( 'CB', 'PS')
GROUP BY l.UserID, l.Tool, e.ADesc, e.SDesc, e.DDesc, e.ALN,
ORDER BY l.Tool, l.UserID;

This assumes that the employee table does not have duplicates, with respect to the join conditions on the Log.

Upvotes: 4

Related Questions