CODEWITHSUNDEEP
mysqlsqlgroup-by
eyurdakul
eyurdakul

Reputation: 912

mysql select only groups having certain length

Lets say, i have a table like this:

table1

  id | val
_____________
   1 | a
   2 | b
   3 | b
   4 | a
   5 | b
   6 | a
   7 | c

and i want to have all rows grouped by val but only if the size of the group is 3. So intended result is:

[1, a], [4, a], [6, a]
[2, b], [3, b], [5, b]

[7, c] should be ignored cause the size of it's group is less than 3.

Note, the solution with having will return one row for each group and i want to see them all.

Upvotes: 4

Views: 1474

Answers (5)

jared
jared

Reputation: 483

You can use the following query:

SELECT  
    id,
    val as letter, 
    (select count(*) FROM things WHERE val=letter) as number
  FROM things
HAVING number >= 3;

Upvotes: 0

Vecchiasignora
Vecchiasignora

Reputation: 1315

try it 

 select sel.id, sel.val 
    from 
        (Select COUNT(*) over (partition by val order by val) rowcount, a.* 
    from table1 a) sel
        where sel.rowcount = 3

Upvotes: -1

jarlh
jarlh

Reputation: 44786

Have a sub-query that returns all val's that occur at least 3 times:

select id, val
from tablename
where val in (select val
              from tablename
              group by val
              having count(*) >= 3)

Upvotes: 1

Jens
Jens

Reputation: 69460

GROUP_CONCAT with HAVING should give yu the requested result:

select Group_concat(id), value from table group by value having Count(*)>=3

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521794

Use a subquery to identify the id values whose groups have three or more members, and then join this to the original table to restrict to the records you want in your result set.

SELECT t1.*
FROM table1 t1
INNER JOIN
(
    SELECT val
    FROM table1
    GROUP BY val
    HAVING COUNT(*) >= 3
) t2
    ON t1.val = t2.val

Output:

enter image description here

Demo here:

Rextester

Upvotes: 3

Related Questions