Clever Geek Handbook

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.

+3


source to share


2 answers


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 .

+2


source


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 .

+2


source






More articles:


All Articles