Improve company productivity with a Business Account.Sign Up

x
?
Solved

oracle counts and group by version 8i

Posted on 2014-03-03
6
Medium Priority
?
238 Views
Last Modified: 2014-03-04
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

0
Comment
Question by:CalmSoul
6 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39901797
Yes it's possible, what have you coded?

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

Author Comment

by:CalmSoul
ID: 39901832
Added count(*) is giving me following error

Lookup Error
ORA-00936: missing expression
0
 
LVL 23

Expert Comment

by:David
ID: 39901941
Post your statement please, otherwise we're missing...
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
LVL 39

Accepted Solution

by:
Geert G earned 2000 total points
ID: 39901944
select GroupName, count(distinct username)
from User
where UID in  (select GUID from group_user)
group by groupname

that should do it
0
 
LVL 23

Expert Comment

by:David
ID: 39901952
hmmm, I was thinking the where subquery and the group by couldn't be used together, but live and learn :)
0
 
LVL 39

Expert Comment

by:Geert G
ID: 39901995
now that you mention that ... hadn't tested it
but indeed ... it works ... also found out after testing
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

584 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question