SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL query

Author

Topic

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-03-06 : 08:42:57

Hi friends,

I have a two table, table names are users,usertype

Users table columns are : usertypeid username

usertype table columns are : typeid,usertype

I want to display the usertype, and total number of users in each usertype...

Please help me, its urgent

Thanks in Advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 08:49:21

SELECT UserTypeID, COUNT(*)
FROM Users
GROUP BY UserTypeID

E 12°55'05.25"
N 56°04'39.16"

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-06 : 08:49:42

What have you tried?


Select a.usertype, count(*)
FROM usertype a inner join users b on a.typeid = b.usertypeid
Group by a.usertype

Poor planning on your part does not constitute an emergency on my part.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-06 : 08:50:31

Or better (just in case you want all types even if not used),

SELECT ut.UserType, COUNT(u.UserTypeID)
FROM UserTypes AS ut
LEFT JOIN Users AS u ON u.UserTypeID = ut.TypeID
GROUP BY ut.TypeID, ut.UserType

(yes, the group by contains BOTH id and name for precaution if name is not unique).

E 12°55'05.25"
N 56°04'39.16"

dhinasql
Posting Yak Master

195 Posts

Posted - 2008-03-06 : 08:53:03

Thanks for ur reply..
I dnt want to display usertypeid, I want to display usertype and number of users....

Pls help me,
Thanks in advance

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-03-06 : 08:56:58