-1

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,))
7
  • Why FROM users whereas you want to count values in the query text (or in CSV string transferred into the query as a parameter)?
    – Akina
    Commented May 15, 2020 at 14:20
  • Well, FROM "what" I must write? Commented May 15, 2020 at 14:50
  • In shown code you must use FIND_IN_SET() function instead of IN operator. cursor.execute('SELECT user_id FROM users WHERE FIND_IN_SET(user_id, %s)', (set_of_user_ids,)).
    – Akina
    Commented May 15, 2020 at 14:52
  • Anyway, this does not help with COUNT() Commented May 15, 2020 at 16:12
  • this is homework and so there are a bunch of the same question see stackoverflow.com/questions/61823790/…
    – nbk
    Commented May 15, 2020 at 17:45

4 Answers 4

0

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.

1
  • It is not suitable for many user_ids, as every select is written by the hands Commented May 16, 2020 at 20:48
0

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);

0

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 |
+------+----------+
3
  • I don't think FROM ( VALUES ... ) is valid MySQL syntax; please provide a reference.
    – Rick James
    Commented May 29, 2020 at 1:13
  • @RickJames VALUES is a DML statement introduced in MySQL 8.0.19 which returns a set of one or more rows as a table. dev.mysql.com/doc/refman/8.0/en/values.html Commented May 29, 2020 at 5:39
  • 1
    Thanks. (It's still warm from the oven!)
    – Rick James
    Commented May 30, 2020 at 1:28
0

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 |
+-----------------------------------------------------+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.