Geting value count from an Oracle Table

Question

I have a table, that contains employees. Since the company I'm working for is quite big (>3k employees) It is only natural, that some of them have the same names. Now they can be differentiated by their usernames, but since a webpage needs a drop-down with all of these users, I need to add some extra data to their names.

I know I could first grab all of the users and then run them through a foreach and add a count to each of the user objects. That would be quite ineffective though. Therefore I'm in need of a good SQL query, that would do something like this. Could a sub-query be the thing I need?

My Table looks something like this:

name ----- surname ----- username

John       Mayer         jmaye

Suzan      Harvey        sharv

John       Mayer         jmay3

Now what I think would be great, if the query returned the same 3 fields and also a boolean if there is more than one person with the same name and surname combination.

vc 74 · Accepted Answer · 2011-08-30 09:14:41Z

5

Adding the flag to Daniel's answer...

SELECT NAME, SURNAME, USERNAME, DECODE(COUNT(*) OVER (PARTITION BY NAME, SURNAME), 1, 'N', 'Y')
FROM
YOUR_TABLE;

Please note that Oracle SQL has no support for booleans (sigh...)

edited Aug 30, 2011 at 9:14

answered Aug 30, 2011 at 7:05

vc 74

38.3k7 gold badges80 silver badges96 bronze badges

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

2 Comments

Janis Peisenieks Over a year ago

Thanks, this worked, but the part with the decode is wrong. It threw an error. This worked though: DECODE(COUNT(*) OVER (PARTITION BY NAME, SURNAME), 1, 'N', 'Y')

vc 74 Over a year ago

@Janis ahh true, sorry I hadn't tried it. I've updated the query accordingly.

Daniel Hilgarth · Accepted Answer · 2011-08-30 06:40:38Z

3

This can be easily done with a count over partition:

SELECT NAME, SURNAME, USERNAME, COUNT(*) OVER (PARTITION BY NAME, SURNAME)
FROM
YOUR_TABLE;

edited Aug 30, 2011 at 6:40

answered Aug 30, 2011 at 6:34

Daniel Hilgarth

175k41 gold badges346 silver badges450 bronze badges

Collectives™ on Stack Overflow

Geting value count from an Oracle Table

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related