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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With