Geting counter from Oracle table

Question

I have a table containing employees. Since the company I'm working on is quite large (> 3k employees). Naturally, some of them have the same name. Now they can be distinguished by their usernames, but since the web page requires a drop-down list with all these users, I need to add some additional data to their names.

I know that I can first capture all users, and then run them through foreach and add an account to each of the user objects. However, this would be very inefficient. So I need a good SQL query that would do something like this. Could a sub-query be what 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 request returned the same 3 fields, as well as logical if there is more than one person with the same first and last name.

Answer 1

Adding a flag to Daniel's answer ...

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

Note that Oracle SQL does not support Boolean (sigh ...)

Answer 2

This can be easily done by counting over a section:

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