How to get total occurrence of a value within its own query?

Question

The reason why I am asking this is due to legacy code, so bare with me

Lets say we have this query

SELECT * FROM table

And this result from it.

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

How could I get the count of how often a user_id appears as another field (without some major SQL query)

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

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

BTW if this is not possible without some major trick, we are just going to skip sorting on that field.

Answer 1

You'll just want to add a subquery on the end, I believe:

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 query as not being a performance monster but it should work.