How can I include all the other fields of table when I'm filtering the data by only 1 field using group by hand having?

So this my query below:

select creditcard
from  user where preferred_card = '1'
and uder_identification_code = 'creditcard' 
group by creditcard having count (1) > 1;

So I'm trying to get list of all the user having more than 1 credit card as their preferred card(basically it's a data clean up since there should only 1 preferred).

But since I'm doing group by and having count, when I tried to put in other fields, the query doesn't run because logic error. How do I make the query show other fields, like lastname, dob from the user table? I'm trying to pull all the list of users so I can do data clean up.

all the changes failed below:

select creditcard, lastname
from  user where preferred_card = '1'
and uder_identification_code = 'creditcard' 
group by creditcard having count (1) > 1 and lastname;`



select *
from  user where preferred_card = '1'
and uder_identification_code = 'creditcard' 
group by creditcard having count (1) > 1 and lastname;

I've tried group by by more than several fields from the same table

Solution

You can query the table with the data you get from group by as such:

select * from user
where creditcard in 
(select creditcard 
from  user 
where preferred_card = '1' and uder_identification_code = 'creditcard'  
group by creditcard having count (1) > 1);