Counting comments

[David Miller]

Christopher Hicks wrote:
> On Sat, 16 Oct 2004, Gervase Markham wrote:
> 
>> Is it possible, either via the GUI or directly in SQL, to say:
>>
>> "Give me a list of all bugs meeting certain criteria, which in addition
>> have (exactly 1|more than 1) comment"?
>>
>> I can do:
>>
>> SELECT bug_id, count(bug_when) AS bug_count FROM longdescs GROUP BY 
>> bug_id;
>>
>> but then I can't say "AND bug_count > 1" - it doesn't like that.
>>
>> At a pinch, I could cope with a solution just for one of the two cases,
>> as I could do the root query and subtract.
> 
> Isn't that what HAVING is for?

Yep, Chris is correct, this works:

mysql> SELECT bug_id, count(bug_when) AS comments FROM longdescs GROUP 
BY bug_id HAVING comments > 400 ORDER BY comments DESC;
+--------+----------+
| bug_id | comments |
+--------+----------+
|  27803 |      700 |
|  18574 |      540 |
|  25537 |      503 |
| 171441 |      430 |
|  82534 |      410 |
+--------+----------+
5 rows in set (9.29 sec)

-- 
Dave Miller      Project Leader, Bugzilla Bug Tracking System
http://www.justdave.net/             http://www.bugzilla.org/