SQL SELECT id and count of items in same table

Question

I have the following SQL table columns...

id | item | position | set
---------------------------    
 1 |   1  |     1    | 1
 2 |   1  |     1    | 2
 3 |   2  |     2    | 1
 4 |   3  |     2    | 2

In a single query I need to get all the ids of rows that match set='1' while simultaneously counting how many instances in the same table that it's item number is referenced regardless of the set.

Here is what I've been tinkering with so far...

SELECT 
    j1.item, 
    (SELECT count(j1.item) FROM table_join AS j2) AS count 
FROM 
    table_join AS j1 
WHERE 
    j1.set = '1';

...though the subquery is returning multiple rows. With the above data the first item should have a count of 2, all the other items should have a count of 1.

Answer 1

This should work:

SELECT
    j.id
,   (SELECT COUNT(*) FROM table_join i WHERE i.item = j.item) AS count
FROM table_join j
WHERE set='1'

This is similar to your query, but the subquery is coordinated with the outer query with the WHERE clause.

Demo.

Answer 2

As an alternative worth testing for performance, you can use a JOIN instead of a dependent subquery;

SELECT tj.id, COUNT(tj2.id) count
FROM table_join tj
LEFT JOIN table_join tj2 ON tj.item = tj2.item
WHERE tj.`set`=1
GROUP BY tj.id

An SQLfiddle to test with.