Count numbers of logins - with names

[SpookiePower]

I have two tables TUser and TSignInLog.

In TUser I have these fields -
iUserID
Firstname
Lastname

In SignInLog I have this field -
iUserID

I now want to count how many times, each user have logged in,
and show it with the users names.

I can do it without there names like this -

SELECT iUserId, count(iUserId) AS Logins
FROM TSignInLog
GROUP BY iUserId
order by iUserId desc;

But with there names, I can't figure it out.

My guess is this, but it wont work -

SELECT Firstname, Lastname, count(iUserId) AS Logins
FROM TUser INNER JOIN TSignInLog on TSignInLog.iUserId=TUser.iUserid
GROUP BY iUserId
order by Logins desc;

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You have to identify which table iUserID comes from. I use table
aliases for readability:

SELECT U.iUserID, U.Firstname, U.Lastname, COUNT(L.iUserId) AS Logins
FROM TUser As U INNER JOIN TSignInLog As L ON L.iUserId = U.iUserid
GROUP BY U.iUserID
ORDER BY COUNT(L.iUserID) DESC;

If you don't want to use the expression of column 3 you can change the
ORDER BY clause to look like this: ORDER BY 3 DESC

Where 3 is the column's ordinal number in the SELECT clause.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR5CdyIechKqOuFEgEQL1eACbBPfz0vD8+UrfFDaO/aab+YAtgZsAnjTf
uauNbMkZ4Uj66VEwboSGgF9M
=b9Q/
-----END PGP SIGNATURE-----

 

[John Spencer]

I think your Order By Clause is the problem. You cannot use a field alias
in the ORDER BY clause

SELECT Firstname, Lastname, count(iUserId) AS Logins
FROM TUser INNER JOIN TSignInLog on TSignInLog.iUserId=TUser.iUserid
GROUP BY iUserId
ORDER BY Count(iUserID) desc;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[SpookiePower]

I'm sorry, but none of them works.

 

[SpookiePower]

I think your Order By Clause is the problem. You cannot use a field alias
in the ORDER BY clause

I get an error that says, that it don't know what table it should
take IUserId from.

 

[John Spencer]

Since you have two fields with the same name, you need to specify both the
table and field name. Also I missed the grouping on FirstName and LastName.

Try this UNTESTED revision of the SQL. I'm assuming that tUser.iUserID is
unique (primary key). FirstName plus LastName will not necessarily be
unique in an organization.

SELECT TUser.iUserid
, Firstname
, Lastname
, Count(TSignInLog.iUserId) AS Logins
FROM TUser INNER JOIN TSignInLog
ON TSignInLog.iUserId=TUser.iUserid
GROUP BY TUser.iUserid, FirstName, LastName
ORDER BY Count(TSignInLog.iUserId) desc;

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..