oracle counts and group by version 8i

CalmSoul
CalmSoul used Ask the Experts™
on
I have following query which give list of users and group, I would like to put counts on Users and group by "GroupName"

Please let me know how it is possible

select UNIQUE UserName, GroupName from User where UID in 
(select GUID from group_user) 

User1	Group1
User2	Group1
User3	Group2
User4	Group3
User1   Group3
User1   Group3
User2   Group3 

Open in new window

OUTPUT

Group1 2
Group2 1
Group3 3

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Yes it's possible, what have you coded?

HINT: Use the COUNT(*) and GROUP BY functions in your query.

Author

Commented:
Added count(*) is giving me following error

Lookup Error
ORA-00936: missing expression
David VanZandtOracle Database Administrator III

Commented:
Post your statement please, otherwise we're missing...
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Oracle dba
Top Expert 2009
Commented:
select GroupName, count(distinct username)
from User
where UID in  (select GUID from group_user)
group by groupname

that should do it
David VanZandtOracle Database Administrator III

Commented:
hmmm, I was thinking the where subquery and the group by couldn't be used together, but live and learn :)
Geert GOracle dba
Top Expert 2009

Commented:
now that you mention that ... hadn't tested it
but indeed ... it works ... also found out after testing

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial