Counting comments

[Bradley Baetz]

On Sat, Oct 16, 2004 at 04:38:18PM +0100, 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"?

They're almost all going to have the initial comment, so you probably 
want > 1.

select bug_id from longdescs where bug_id in (SELECT id from 
bugs where ....) GROUP BY bug_id HAVING count(*) > 1

In mysql you'll have to do it as a temp table + join.

> 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.

You have to do HAVING count(bug_when) > 1 - HAVING runs after GROUP BY. 
Depending on your myqsl version, you may or may not have to have the 
expression in the having as part of the columns you select.

Bradley