COUNT for IN statement (count from scope, not from a table)

Question

My question is pretty simple:

I want to select user_id, count(user_id) from a scope (not from a table). Is this even possible?

Example:

SELECT user_id FROM users WHERE user_id IN (1, 2, 2, 3, 3, 3, 4, 4, 5);

DESIRED OUTPUT:

id| count
1 | 1
2 | 2
3 | 3
4 | 2
5 | 1

My really code:

set_of_user_ids = (1, 2, 2, 3, 3, 3, 4, 4, 5)
cursor.execute('SELECT user_id FROM users WHERE user_id IN %s', (set_of_user_ids,))

Answer 1

I think something like that should work :

select a.user_id, count(1) as count, count(u.user_id) as num_in_users_table
from 
(select 1 as user_id 
union all
select 2 
...
union all
select 10
)a
left join users u on u.user_id=a.user_id
group by a.user_id

Update - it should be union all if you want duplicates to be counted properly.

Answer 2

You can use COUNT() function even for not tables with AS keyword.

SELECT user_id, COUNT(user_id) FROM (SELECT user_id FROM scope) AS some_name GROUP BY user_id ORDER BY COUNT(user_id);

Answer 3

You can use VALUES statement to store scope related values & then can GROUP BY and count values from it.

 select col1, count(*)
 from (values row(1),row(2),row(3),row(3),row(4),row(2)) as temp(col1)
 group by col1;
+------+----------+
| col1 | count(*) |
+------+----------+
|    1 |        1 |
|    2 |        2 |
|    3 |        2 |
|    4 |        1 |
+------+----------+

Answer 4

Maybe this will help?

To find the number of items in a string. This will work for a given delimiter. It does not de-dup.

mysql> SET @list := '1,22,333,4567,9,8';


mysql> SELECT LENGTH(@list) - LENGTH(REPLACE(@list, ',', '')) + 1;
+-----------------------------------------------------+
| LENGTH(@list) - LENGTH(REPLACE(@list, ',', '')) + 1 |
+-----------------------------------------------------+
|                                                   6 |
+-----------------------------------------------------+