CODEWITHSUNDEEP
mysqlsql
Opolik
Opolik

Reputation: 23

Filter on count result and display all results

I'm trying to display entries that have a count result under a certain value. For instance, this query gives the following result on mySQL:

SELECT a, b, count(*)
FROM myTable
GROUP BY a, b
ORDER BY count;

+----+---+-------+  
| a  | b | count |  
+----+---+-------+  
| OK | - | 1     |  
| KO | - | 1     |  
| OK | + | 3     |  
| KO | - | 4     |  
+----+---+-------+

How may I display all entries that have a count result under 4 ? i.e:

+----+---+-------+  
| a  | b | count |  
+----+---+-------+  
| OK | - | 1     |  
| KO | - | 1     |  
| OK | + | 3     |  
| OK | + | 3     |  
| OK | + | 3     |  
+----+---+-------+

Upvotes: 1

Views: 25

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

Use a HAVING clause and a JOIN:

SELECT t.*
FROM mytable t JOIN
     (SELECT a, b, count(*) as cnt
      FROM myTable
      GROUP BY a, b
      HAVING cnt < 4
     ) tt
     ON t.a = tt.a and t.b = tt.b
ORDER BY tt.cnt;

Upvotes: 1

Related Questions