Invalid column error in SQL Server 2008

Question

I am trying to run the following query,

DECLARE @p_UserName as nvarchar(100)
DECLARE @p_Department as int
DECLARE @p_Section as int
DECLARE @p_SubSection as int
DECLARE @p_PermissionGroup as int
DECLARE @p_DistributionGroup as int
DECLARE @p_Permission as nvarchar(100)
DECLARE @p_IfPerChecked as bit
DECLARE @p_Role as int

SET @p_UserName = ''
SET @p_Department = NULL
SET @p_Section = NULL
SET @p_SubSection = NULL
SET @p_PermissionGroup = NULL
SET @p_DistributionGroup = NULL
SET @p_Permission = ''
SET @p_Role = NULL

SELECT Users.EnglishName,
    (SELECT Designation.TitleEnglish FROM Designation WHERE Users.Designation = Designation.ID) AS [Role],
    (SELECT Department.TitleEnglish FROM Department WHERE Users.DepartmentID = Department.ID) AS [Department],
    (SELECT Section.TitleEnglish FROM Section WHERE Users.SectionID = Section.SectionID) AS [Section],
    (SELECT SubSection.TitleEnglish FROM SubSection WHERE Users.SubSectionID = SubSection.SubSectionID) AS [Sub-Section],
    (SELECT Groups.TitleEnglish FROM Groups WHERE 
        (SELECT UserRights.GroupID FROM UserRights WHERE Users.UserID = UserRights.UserID) = Groups.GroupID
        AND Groups.IsDistribution = 0
    ) AS [Permissions Group],
    (SELECT Groups.TitleEnglish FROM Groups WHERE 
        (SELECT UserRights.GroupID FROM UserRights WHERE Users.UserID = UserRights.UserID) = Groups.GroupID
        AND Groups.IsDistribution = 1
    ) AS [Distribution Group],
    (CASE
        WHEN Users.ApplyUserRights = 1 THEN dbo.fn_GetUserPermissions('Users', Users.UserID)
        WHEN Users.ApplyUserRights = 0 THEN dbo.fn_GetUserPermissions('Groups', (SELECT GroupID FROM UserRights WHERE Users.UserID = UserRights.UserID))
    END) AS [Permissions]
FROM Users WHERE 
    ((@p_UserName <> NULL OR @p_UserName <> '') AND @p_UserName = Users.UserName) OR
    ((@p_Department <> NULL OR @p_Department <> 0) AND @p_Department = Users.DepartmentID) OR
    ((@p_Section <> NULL OR @p_Section <> 0) AND @p_Section = Users.SectionID) OR
    ((@p_SubSection <> NULL OR @p_SubSection <> 0) AND @p_SubSection = Users.SubSectionID) OR
    ((@p_PermissionGroup <> NULL OR @p_PermissionGroup <> 0) AND @p_PermissionGroup = (
            SELECT Groups.GroupID FROM Groups WHERE 
                (SELECT UserRights.GroupID FROM UserRights WHERE Users.UserID = UserRights.UserID) = Groups.GroupID
                AND Groups.IsDistribution = 0
        )) OR
    ((@p_DistributionGroup <> NULL OR @p_DistributionGroup <> 0) AND @p_DistributionGroup = (
            SELECT Groups.GroupID FROM Groups WHERE 
                (SELECT UserRights.GroupID FROM UserRights WHERE Users.UserID = UserRights.UserID) = Groups.GroupID
                AND Groups.IsDistribution = 1
        )) OR
    (1 = CASE
        WHEN Users.ApplyUserRights = 1 AND @p_Permission = 'AllowChangePassword' THEN 0
        ELSE dbo.fn_CheckPemission(@p_Permission, UserID)
    END) OR
    ((@p_Role <> NULL OR @p_Role <> 0) AND @p_Role = Users.Designation) OR
    (1 = 1)
GROUP BY CASE @GroupBy
    WHEN 'DepartmentID' THEN Users.DepartmentID
    WHEN 'SectionID' THEN Users.SectionID
    WHEN 'SubSectionID' THEN Users.SubSectionID
    ELSE Users.EnglishName
END

And I keep getting the error

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

Please help!

score 1 · Answer 1 · answered Sep 08 '14 at 08:58

1

Like the message states, you need to group by the EnglishName column unless it is used in an aggregate function(Max or sum or likewise).

answered Sep 08 '14 at 08:58

danish

5,550
2
25
28

score 0 · Answer 2 · answered Sep 08 '14 at 09:01

0

The issue is evident from your error message. Use GROUP BY in case of aggregate functions.

answered Sep 08 '14 at 09:01

James

172
2
10

but it has to be conditional, the group by i mean and i don't want to group by on users.englishtitle – hira Sep 08 '14 at 09:13
take a look at this http://stackoverflow.com/questions/4611897/group-by-aggregate-function-confusion-in-sql – James Sep 08 '14 at 09:38

Invalid column error in SQL Server 2008

2 Answers2