CODEWITHSUNDEEP
phpmysqlsqlselect
cream
cream

Reputation: 1129

Counting/Grouping with MySQL

Is it possible to return all rows that belong to a group with less than 3 rows using just SQL or do I have to use some PHP to get this result?

For example consider this table

 ___________________
| id | group | name |
|:::::::::::::::::::|
| 0  |   1   | bob  |
| 1  |   2   | ted  |
| 2  |   1   | jack |
| 3  |   3   | lars |
| 4  |   3   | dane |
| 5  |   1   | joe  |
| 6  |   2   | kid  |
| 7  |   2   | neil |

I would want to return rows 3 and 4 because they belong to a group which has less than 3 rows.

Is there an SQL only solution?

Upvotes: 2

Views: 88

Answers (3)

Charles Bretana
Charles Bretana

Reputation: 146469

Select * From table
Where `group` in 
     (Select `group` from table 
      group by `group`
      having count(*) < 3)

Upvotes: 2

MISJHA
MISJHA

Reputation: 1008

You can try this:

SELECT t.`id`, t.`group`, t.`name`, tt.`rows`
FROM `table` t
JOIN (SELECT `group`, COUNT(*) AS rows FROM table GROUP BY `group`) tt
ON tt.`group` = t.`group`
HAVING tt.`rows` < 3

Upvotes: 3

sgeddes
sgeddes

Reputation: 62831

I think something like this should work for you joining the table back to itself:

SELECT T.Id, T.`Group`, T.Name
FROM YourTable T
   JOIN (
    SELECT `Group`, COUNT(1) cnt
    FROM YourTable
    GROUP BY `Group`
    ) T2 ON T.`Group` = T2.`Group` AND T2.cnt < 3

You need to put backticks around the column name Group as I believe it's a reserved word.

SQL Fiddle Demo

Upvotes: 1

Related Questions