query problem (group by) - 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

SQL Server 2000 Forums

Transact-SQL (2000)

query problem (group by)

Author

Topic

tutufool
Starting Member

14 Posts

Posted - 2006-11-14 : 08:08:04

I have tables like:

username userType

user1 A

user2 A

user3 B

user4 C

I want one signle query to return

userType userCount percent

A 2 50%

B 1 25%

C 1 25%

can anyone help me ?

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-14 : 08:11:38

Try

Select usertype, count(*) as usercount, count(*)*1.0/(select count(usertype) from table)*100 as percent
from table group by usertype

Madhivanan

Failing to plan is Planning to fail

tutufool
Starting Member

14 Posts

Posted - 2006-11-14 : 08:30:25

thank you, Madhivanan
your sql works, but I have one more question:
can we avoid using child query?
I mean the real query will not be so simple, it's not a simple table, it's a complicated query with lots of joins, so like you said, "count(*)*1.0/(select count(usertype) from table)", I have to repeat the big query here.

quote:
Originally posted by madhivanan

Try

Select usertype, count(*) as usercount, count(*)*1.0/(select count(usertype) from table)*100 as percent
from table group by usertype

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-14 : 08:42:06

[code]SELECT UserType,
COUNT(*) AS UserCount,
COUNT(*) / MIN(x.Total) AS Percentage
FROM @Test
CROSS JOIN (SELECT 1.0 * COUNT(*) AS Total FROM @Test) x
GROUP BY UserType[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources