[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 237
  • Last Modified:

oracle counts and group by version 8i

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
CalmSoul
Asked:
CalmSoul
1 Solution
 
MikeOM_DBACommented:
Yes it's possible, what have you coded?

HINT: Use the COUNT(*) and GROUP BY functions in your query.
0
 
CalmSoulAuthor Commented:
Added count(*) is giving me following error

Lookup Error
ORA-00936: missing expression
0
 
DavidSenior Oracle Database AdministratorCommented:
Post your statement please, otherwise we're missing...
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Geert GruwezOracle dbaCommented:
select GroupName, count(distinct username)
from User
where UID in  (select GUID from group_user)
group by groupname

that should do it
0
 
DavidSenior Oracle Database AdministratorCommented:
hmmm, I was thinking the where subquery and the group by couldn't be used together, but live and learn :)
0
 
Geert GruwezOracle dbaCommented:
now that you mention that ... hadn't tested it
but indeed ... it works ... also found out after testing
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now