EL323 EL323 - 1 month ago 6
SQL Question

Sql Query to select a column value with an aggregate funtion?

Table: (tblUser)

______________________________
|Column Name | Data Type |
|------------------------------|
| ID | bigint |
| UserName | nvarchar(100) |
| Password | nvarchar(100) |
| Email | nvarchar(200) |
| UserType | bit |
|_____________|________________|


This is what I have tried:

CREATE Procedure spAutheticateUser
@UserName nvarchar(100),
@Password nvarchar(200)
as
Begin
Declare @Count int
Declare @Type bit

Select @Count = COUNT(UserName), @Type = UserType from tblUsers
where [UserName] = @UserName and [Password] = @Password

If (@Count = 1 and @Type = 'True')
Begin
Select 1 as ReturnCode
End
Else If (@Count = 1 and @Type = 'False')
Begin
Select 2 as ReturnCode
End
Else
Begin
Select -1 as ReturnCode
End
End


This is the error I get on SQLServer:


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


Actually I want to call this stored procedure behind loginButton_click function. I want to validate User information and I also want to check which type the user belongs to. Depending on the UserType I will redirect to a specific page.

Kindly guide me on how to achieve the functionality. Thanks.

Answer

Change Your select statement as below:

Select @Count = COUNT(UserName), @Type = UserType from tblUsers
where [UserName] = @UserName and [Password] = @Password
GROUP By UserType 

You are going to use aggregate function, so you have to group by remain column , which you have select

Comments