Postgresql Total elements and affiliation users I have a two table that contains varchar:
items_tags_sets: item_id, user_id, tag
and
users: user_id, name.
How to make a query that displays a table with columns:

Certain Item
Tags belonging to a specific element.
Total Number of duplicate tags.
The fact that a particular user has set this tag, like other users.

I need a result like this:
----------------------------------------------
| item_id |    tag    | tags_count | its_tag |
|--------------------------------------------|
|   it1   | tag_test1 |      3     |  usr1   |
|   it1   | tag_test3 |      2     |         |
|   it1   | tag_test5 |      1     |         |
----------------------------------------------

Link to code: https://rextester.com/live/KXLJX41855
You need to remove user_id from your group by clause. But that requires putting it into an aggregate function in the SELECT list. One way to do that is to use string_agg()
SELECT its.item_id,
       its.tag,
       count(its.tag) as tags_count,
       string_agg(u.user_id, ',') as user_ids
FROM items_tags_sets its
  LEFT JOIN users u on u.user_id = its.user_id and u.user_id = 'usr1'
WHERE its.item_id = 'it1'
GROUP BY its.item_id, its.tag
order by its.item_id, its.tag;

Online example