SQL Basics

Question

Post reply

SQL Basics

evanburen

SSC Rookie

Points: 27
More actions
December 17, 2002 at 1:40 pm

#101333

I'm using this SQL to get a count of each UserID in my table.
SELECT UserID, COUNT(UserID) AS UserCount FROM TUserLogs GROUP BY UserID ORDER BY COUNT([UserID]) DESC
I would also like to include the user's name in the results from the TSubscribers table. How would I include the 'Name' field from Tsubscribers in the query above? I would like the results to be
UserID, Name, UserCount
These are my 2 tables and the fields in them
TUserLogs -
UserID
TSubscribers -
UserID
Name
Thanks.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

bp SSCarpal Tunnel Points: 4656 More actions · Answer 1

SELECT y.name, COUNT(UserID) AS UserCount

FROM TUserLogs x inner join tsubscribers y on x.userid = y.userid

GROUP BY y.name

ORDER BY COUNT([UserID]) DESC

gljjr SSChampion Points: 10080 More actions · Answer 2

Try

SELECT T.UserID, T.Name, UserCount = COUNT(T.UserID)

FROM TUserLogs U

JOIN TSubscribers S ON U.UserID = S.UserID

GROUP BY T.UserID, T.Name

ORDER BY COUNT(T.UserID) DESC

Gary Johnson

DBA

Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

evanburen SSC Rookie Points: 27 More actions · Answer 3

Thanks for the quick response. I get the error message in QA:

Server: Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'UserID'.

Server: Msg 209, Level 16, State 1, Line 1

Ambiguous column name 'UserID'.

evanburen SSC Rookie Points: 27 More actions · Answer 4

Thanks Gary - for your code I get:

Server: Msg 107, Level 16, State 2, Line 1

The column prefix 'T' does not match with a table name or alias name used in the query.

bp SSCarpal Tunnel Points: 4656 More actions · Answer 5

SELECT y.name, COUNT(y.UserID) AS UserCount

FROM TUserLogs x inner join tsubscribers y on x.userid = y.userid

GROUP BY y.name

ORDER BY COUNT([y.UserID]) DESC

gljjr SSChampion Points: 10080 More actions · Answer 6

DOH!

Try changing the T to U... Helps if I don't try to do three things at once!

Gary Johnson

DBA

Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

evanburen SSC Rookie Points: 27 More actions · Answer 7

sorry bp, i still get the error message

Server: Msg 207, Level 16, State 3, Line 1

Invalid column name 'y.UserID'.

gljjr SSChampion Points: 10080 More actions · Answer 8

Evan,

I would suggest you read up on Aliasing in SQL Books Online. Basically what we have done in my query was to alias the table name so that we didn't have to type so much.

Therefore when you have

Select y.Col1 x

FROM table1 y

x is the alias for the column Col1 and y is the alias for table1. Clear as mud?

Gary Johnson

DBA

Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

bp SSCarpal Tunnel Points: 4656 More actions · Answer 9

whooops...

instead of...

ORDER BY COUNT([y.UserID]) DESC

try...

ORDER BY COUNT(y.[UserID]) DESC

(notice where the first [ was ...)

Billy

evanburen SSC Rookie Points: 27 More actions · Answer 10

Thanks much to both of you. This was a good lesson in aliasing for me and I plan on doing some more reading and practice.