There is a data set which suppose to be 1-to-1 mapping. For example,
userId, deviceId
aaa, 12345
bbb, 22398
ccc, 43284
However, there is a case where one deviceId has many userId.
userId, deviceId
ddd, 12094
eee, 12094
fff, 12094
ggg, 459834
hhh, 459834
iii, 459834
jjj, 459834
What SQL can list up those one-to-many entries only? Imagine, there is a lot of one-to-many entries. I don't need 1-to-1 entry in the result. So, what I want to see as result is something like,
deviceId, _cnt
12094, 3
459834, 4