I've been trying to figure out the right query syntax for this.

I have this object (simplified):


I want to query that returns all the Bugs for a particular testRun and how many TestOutcomes have that Bug associated with it.

This query returns each of the Bugs, but I don't know how to get the count along with it:

If I try making it I get an error about not in aggregate function or group by.

I've also tried variations like

but this gets the same error as well. I have tried a half dozen variations of these and can't quite seem to get it right, although I'm sure it's simple.

how about

select count(distinct outc.bug) from TestOutcome outc where testRun = ? and outc.bug is not null group by outc.bug

try it

Nope, that just gives me a list of 1's. I can already get the total count of the bugs, but I can't seem to get the associated bugs along with it.

So the query I already posted returns each of the bugs that I want, but not the total count of TestOutcomes with those bugs.

If I do this I get the total count of each of the bugs, (in this case 4,8,1,6) but I don't know which bug is associated with each total. So really, I want the results from query A and query B in the same query, but my attempts to combine them have been unsuccessful.

But if I add ", outc.bug" to the query B, so it's like this:
then I get "SQLException: Not in aggregate function or group by clause"

If I leave off the "group by" in the last query and just do this: then I still get "SQLException: Not in aggregate function or group by clause"

gets me "QuerySyntaxException: unexpected token: , near line 1, column 23"

Anyway, what I'm trying to do seems like it should be really simple, but for whatever reason I can't get the syntax right. If somebody who knows the answer could reply it would be very appreciated.

You need to use two queries. One for the count and one for the data. Or you can count the number of items returned by the "data" query.

Eg:

I could do the distinct query and then do an additional query for each item, but that seems like way too many queries -- if I have 10 distinct items then I've have to do 1 + 10 queries or 11 scans of the same table(s). I'm surprised I can't do this all in one query that would prevent the redundancy. I still suspect there's a syntax that would do it for me. I thought it would be simple, but maybe I have to do a subquery or something more complicated?

I figured this out, here's the query that worked for me:



Now that I'm retrieving the correct values, I'd like to get them ordered descending by the count determined in the sub query, but again the syntax is elluding me. Here's what I tried, adding "as bugcount" and "order by bugcount" to the query:

attempt 1:


attempt 2:


but both of these result in "SQLException: Column not found: BUGCOUNT in statement..."