Clever Geek Handbook

How to find out a 1-to-many input

There is a dataset that suggests this is a 1-to-1 mapping. For example,

userId, deviceId
aaa, 12345
bbb, 22398
ccc, 43284

However, there is a case where one deviceId has many userIds.

userId, deviceId
ddd, 12094
eee, 12094
fff, 12094
ggg, 459834
hhh, 459834
iii, 459834
jjj, 459834

That SQL can only display those that are in many? Imagine there are many one-to-many records. I don't need a 1 to 1 record in the result. So, what I want to see as a result is something like

deviceId, _cnt
12094, 3
459834, 4
+3


source to share


3 answers


Hi you can use COUNT and then filter the ones that have COUNT greater than 1:



SELECT deviceId
       , COUNT(DISTINCT userId) AS uct
       , COUNT(userId) AS ct
  FROM table
 GROUP BY deviceId
HAVING ct > 1 
   AND uct > 1
0


source


It is recommended to use the column name instead of count (*). It will also improve performance in case of a large dataset.



SELECT deviceId, 
COUNT(userId) AS user_count
FROM table
GROUP BY deviceId
HAVING COUNT(userId)> 1
+2


source


A simple account does this:

select deviceId, count(*) _cnt
from mytable
group by 1
having count(*) > 1

To list all entries that are not 1-1:

select userId, deviceId
from mytable
where deviceId in (
    select deviceId
    from mytable
    group by 1
    having count(*) > 1
)

or

select distinct a.userId, b.deviceId
from mytable a
join mytable b on b.deviceId = a.deviceId
    and b.userId != a.deviceId
+1


source






More articles:


All Articles