CODEWITHSUNDEEP
mysqlsqlcount
Thomas
Thomas

Reputation: 11

mysql select records where value occurs

I hope you can help me with this one. I've been looking for ways to set up a MySQL query that selects rows based on the number of times a certain value occurs, but have had no luck so far. I'm pretty sure i need to use count(*) somewhere, but i can only found how to count all values or all distinct values, instead of counting all occurences.

I have a table as such:

info setid
--   --
A    1
B    1
C    2
D    1
E    2
F    3
G    1
H    3

What i need is a query that will select all the lines where a setid occurs a certain number (x) of times.

So using x=2 should give me

C    2
E    2
F    3
H    3

because both setIds 2 and 3 each occur two times. Using x=1 or x = 3 should not give any results, and choosing x=4 should give me

A    1
B    1
D    1
G    1

Because only setid 1 occurs 4 times.

I hope you guys can help me. At this point i've been looking for the answer for so long that i'm not even sure this can be done in MySQL anymore. :)

Upvotes: 1

Views: 283

Answers (2)

Gervs
Gervs

Reputation: 1397

Consider the following statement that uses an uncorrelated subquery:

SELECT ... FROM t1 WHERE t1.a IN (SELECT b FROM t2); The optimizer rewrites the statement to a correlated subquery:

SELECT ... FROM t1 WHERE EXISTS (SELECT 1 FROM t2 WHERE t2.b = t1.a); If the inner and outer queries return M and N rows, respectively, the execution time becomes on the order of O(M×N), rather than O(M+N) as it would be for an uncorrelated subquery.

But this time the subquery in Fuzzy Tree's solution is complety superfluous:

SELECT
     set_id,
     GROUP_CONCAT(info ORDER BY info) infos
     COUNT(*) total
FROM
    tablename
GROUP_BY set_id
    HAVING COUNT(*) = 2

Upvotes: 0

FuzzyTree
FuzzyTree

Reputation: 32392

select * from mytable
where setid in (
    select setid from mytable
    group by setid
    having count(*) = 2
)

you can specify the # of times a setid needs to occur in the table in the having count(*) part of the subquery

Upvotes: 1

Related Questions