How to get the total number of values ​​as part of my own query?

Question

Let's say we have this request

SELECT * FROM table

And this result from that.

id | user_id
------------
1  | 1
------------
2  | 1
------------
3  | 2
------------
4  | 1

How can I get a counter on how often user_id appears as another field (without any serious SQL query)

id | user_id | count
--------------------
1  | 1       | 3
--------------------
2  | 1       | 3
--------------------
3  | 2       | 1
--------------------
4  | 1       | 3

We have this value currently in the code, but we are implementing sorting by this table, and I would like to be able to sort in the SQL query.

By the way, if this is not possible without any serious trick, we just skip sorting by this field.

Answer 1

You just want to add a subquery at the end, I suppose:

SELECT
    t.id,
    t.user_id,
    (SELECT COUNT(*) FROM table WHERE user_id = t.user_id) AS `count`
FROM table t;

Answer 2

SELECT o.id, o.user_id, (
    SELECT COUNT(id) 
    FROM table i 
    WHERE i.user_id = o.user_id 
    GROUP BY i.user_id
) AS `count`
FROM table o

I suspect this request is not a performance monster, but it should work.