CODEWITHSUNDEEP
sqlt-sqlaggregate-functions
Redtopia
Redtopia

Reputation: 5247

"Column invalid..." using COUNT function in SQL Server

In SQL Server 2K8 R2, I have a table that tracks user activity with these columns (table is named UserActivity):

userFK: nvarchar(35)   // "user1", "user2", "user3", etc
action: nvarchar(35)   // "login", "logout", "vote", "post", "comment", etc

I want to run a query that gets the number of times each user logged-in (where action = "login") that returns:

userFK      ctActions
-------     ---------
"user1"         50
"user2"         45
"user3"         29

The query I thought would work is this:

SELECT UserActivity.userFK, COUNT(*) AS ctActions
FROM UserActivity 
WHERE [action] = 'login'

But SQL Server is giving me the error:

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

What am I doing wrong?

Upvotes: 0

Views: 422

Answers (2)

saurabhk
saurabhk

Reputation: 140

Select * from UserActivity group by userfk

Upvotes: 0

Ritesh kumar
Ritesh kumar

Reputation: 278

Include UserActivity.userFK in the group by clause, because you don't use this column in an aggregate function :

SELECT UserActivity.userFK, COUNT(*) AS ctActions
FROM UserActivity 
WHERE [action] = 'login'
GROUP BY UserActivity.userFK

Upvotes: 3

Related Questions