SQL: Counting number of occurances over multiple columns in Oracle

Question

Cannot get my head around the SQL that will return the number of times a user has accessed a particular service. Think it might require a nested count and select, but cannot get my head around it.

The data looks like this:

UserID  Service
---------------
1       Map1
1       Map2
1       Map1
2       Map1
2       Map2
3       Map4
3       Map2
3       Map2
3       Map2
...     ...

And the desired kind of output is something along the lines of this:

UserID  Service  TimesAccessed
------------------------------
1       Map1     2
1       Map2     1
2       Map1     1     
2       Map2     1
3       Map3     3
3       Map4     1
...     ...      ...

Any help would be much appreciated.

David M · Accepted Answer · 2009-04-08 11:07:27Z

9

I think this does it:

SELECT  UserID, Service, COUNT(UserID) TimesAccessed
FROM    Table
GROUP BY UserID, Service

answered Apr 8, 2009 at 11:07

David M

73.2k13 gold badges164 silver badges187 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bravax · Accepted Answer · 2009-04-08 11:07:27Z

2

Something like this?

select  
    userid, service, count(userid)  
 from   
    table  
 group by  
    userid, service

answered Apr 8, 2009 at 11:07

Bravax

10.5k8 gold badges44 silver badges68 bronze badges

3 Comments

Bravax Over a year ago

No problem, glad we could help.

David M Over a year ago

Sorry, think you actually pipped me by a split second as well!

Bravax Over a year ago

It's fine, this once, don't do it again. Your answer was better formatted and complete though.

Collectives™ on Stack Overflow

SQL: Counting number of occurances over multiple columns in Oracle

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related