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.

Answer 1

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...)

Answer 2

This can be easily done with a count over partition:

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