ORACLE SELECT ID FROM TABLE GROUP BY, ERROR "not a GROUP BY expression"

Question

im trying Select "UID" from a query about number of repetition of user emails, but i need to get the UID's of users with this Email, im not sure how to do this query on Oracle SQL.

I did this query first:

SELECT EMAIL, COUNT("UID") AS NUMREPET FROM USER
GROUP BY (EMAIL) HAVING COUNT (EMAIL) > 1;

And the i get something like:

EMAIL                       NUMREPET
------------------------   ----------
[email protected]                 3    
richard@com                     2    
[email protected]                1    
[email protected]                        1    

How can i get the UID of query? i tried with

SELECT "UID", EMAIL, COUNT("UID") AS NUMREPET FROM USER 
GROUP BY (EMAIL) HAVING COUNT (EMAIL) > 1;

But then i got this error "00979. 00000 - "not a GROUP BY expression".

To clearfull i want to get something like:

"UID"       EMAIL                       NUMREPET
-----   ------------------------   ----------
1787    [email protected]                 3    
1788    [email protected]                 3    
1789    [email protected]                 3    
1000    richard@com                     2    
1001    richard@com                     2           
1687    [email protected]                1    
2001    [email protected]                        1 

Answer 1

You could

select email, count(uid) as numrepet
  from user
 group by email
 where count(uid) > 1

to give you the email addresses that have more that one user id.

You can then join this with your user table to get the user ids that have those emails.

select user.uid, user.email, repeatedemails.numrepet
  from user
  join (select email, count(uid) as numrepet
          from user
         group by email
         where count(uid) > 1) as repeatedemails
     on user.email = repeatedemails.email

I have not used oracle in a long time, so the specific syntax may be a little off, but you should get the idea from this.