Skip to content
Advertisement

How to count the number of entries in the list when requesting Select?

I apologize for the possible incorrectness in the presentation, I use a translator. Let’s say there is a users table in which there is an id field. And there is a list that lists the id numbers and some of them are repeated. My query

select id, count(*)
from users
where id in (3, 10, 10, 10)
group by id;

returns the following 3 – 1, 10 – 1. And I would like to get 3 – 1, 10 – 3, and so on. Is it possible to get it somehow? UPD. The data in the list (3, 10, 10, 10) is just an example, the exact number of digits is not known because they are returned from another question.

Advertisement

Answer

You would need to use a join. You can put the values in a derived table for this:

select id, count(*)
from users u join
     (select 3 as id union all
      select 10 as id union all
      select 10 as id union all
      select 10 as id union all
     ) i
     using(id)
group by id;
User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement