How to set maximum and minimum group sizes to get records, where count & lt; 5
and count & gt; 2
?
At the moment I’m using this query:
SELECT id, info, COUNT (*) as count
FROM notification
GROUP BY info ORDER BY count DESC;
Example:
+ ---- + ------- +
| id | info |
+ ---- + ------- +
| 1 | Admin |
| 2 | User |
| 3 | User |
| 4 | User |
| 5 | User |
| 6 | User |
| 7 | Admin |
| 8 | Admin |
| 9 | Test |
| 10 | Test |
+ ---- + ------- +
Desired output:
+ ---- + ------- + -------- +
| id | info | count |
+ ---- + ------- + -------- +
| 1 | Admin | 3 |
+ ---- + ------- + -------- +
Answer 1, authority 100%
Use the parameter HAVING
:
SELECT id, info, COUNT (*) as count
FROM notification
GROUP BY info
HAVING count (*) & gt; 2
and count (*) & lt; 5
ORDER BY count DESC;
Example at sqlfiddle .
Answer 2, authority 29%
If the table is large, then it would be more correct to wrap it in another query and write a condition in where. It will work faster.
SELECT * FROM (
SELECT id, info, COUNT (*) as count
FROM notification
GROUP BY info
) a
WHERE a.count & gt; 2 AND a.count & lt; 5