EL323 EL323 - 1 year ago 73
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)
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')
Select 1 as ReturnCode
Else If (@Count = 1 and @Type = 'False')
Select 2 as ReturnCode
Select -1 as ReturnCode

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 Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download