CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Jack
Jack

Reputation: 7547

Why is Count(*) returning unexpected number?

This is my query:

Select COUNT(*) 
From 
    Users u
    Inner Join 
    UsersLoginHistory uh On u.UserID = uh.UserID 
Where 
    1 = 1   
    And 
    u.AccountID = 37 
Group By u.UserID

What I'd like to be able to get is Count(*) should be returning a number after grouping on u.UserId. But it returns the Count(*) before the group by is made.

So I can rewrite the above query as:

Select COUNT(*)
From (
    Select   u.Username   
    From 
        Users u
        Inner Join 
        UsersLoginHistory uh On u.UserID = uh.UserID 
    Where 
        1 = 1   
        And 
        u.AccountID = 37 
    Group By u.UserID
) v

But I need to find out why is the Count(*) returning records before a group by is made and how can I fix the 1st query itself.

EDIT: Sample Records

Users table

UserId     Username
102        tom.kaufmann

UserLoginHistory table

UsersLoginHistoryID UserID  LoginDateTime         LogoutDateTime         IPAddress
1                    102    2012-09-28 01:16:00 NULL                 115.118.71.248
2                    102    2012-09-28 01:29:00 2012-09-28 01:29:00  127.0.0.1
3                    102    2012-09-28 01:32:00 2012-09-28 01:32:00  127.0.0.1
4                    102    2012-09-28 01:41:00 NULL                 115.118.71.248
5                    102    2012-09-28 01:43:00 2012-09-28 07:04:00  115.118.71.248

and so on..

Haven't writted every single record in this DB.

Upvotes: 1

Views: 144

Answers (5)

Magnus Eklund
Magnus Eklund

Reputation: 415

COUNT is an aggregate function and this is how it's supposed to work. You get count per grouping.

In your first query you are querying the number of userloginhistory per user. In your second query you are querying number of users with login history.

http://msdn.microsoft.com/en-us/library/ms173454.aspx

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125254

But I need to find out why is the Count(*) returning records before a group by is made and how can I fix the 1st query itself

It is counting the number of lines for each UserID (number of logins), which is exactly how group by is supposed to work.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453298

Based on your second query which you say returns the desired results (and assuming UserID is the PK of Users) I presume this is what you need

SELECT Count(UserID)
FROM   Users u
WHERE  u.AccountID = 37
       AND EXISTS (SELECT *
                   FROM   UsersLoginHistory uh
                   WHERE  u.UserID = uh.UserID)

This will be more efficient than expanding out all the joined rows then collapsing them again with Group By u.UserID and counting the number of rows that result.

Upvotes: 2

Vinnie
Vinnie

Reputation: 3929

This should provide you a list of UserIds and the count of entries in the UsersLoginHistory table.

SELECT u.UserId
, COUNT(uh.*)
FROM Users u
INNER JOIN UsersLoginHistory uh ON u.UserID = uh.UserID
WHERE u.AccountID = 37
GROUP BY u.UserID

Upvotes: 0

Mik378
Mik378

Reputation: 22171

Change the first line to:

Select COUNT(*), u.UserID

Upvotes: 1

Related Questions