SQL SELECT id and number of elements in one table
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 one query, I need to get all the row ids that match set='1'
while counting how many instances in the same table that it references item
are referenced regardless set
.
Here's what I've been doing 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';
... although the subquery returns multiple rows. With the above data, the first item must have a count of 2, all other items must have a count of 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 request, but the subquery is consistent with the outer offer request WHERE
.
Demo .
Alternatively, for performance testing, you can use the JOIN
dependent subquery instead of;
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
SQLfiddle for testing with .