Why do I have to also SELECT an aggregate column to be able to use GROUP BY, if the only SELECTed column is already in GROUP BY?

Question

In the data used by the formula below, column C contains a list of product titles (these are not unique, and many are blank); column Q contains the category for each product, and column F contains that product's sales. If possible, I'd like to avoid posting any actual data (as it belongs to my employer), but I can mock up some bogus rows in the identical format if it's deemed necessary.

What I want to do is to produce a list of the non-blank product titles in a certain category with the highest aggregate sales, sorted in descending order, limited to 25 entries.

The following formula seems to me to be perfectly legitimate:

=QUERY('Raw Data'!A:Q, "Select C where Q = 'foo' and C != '' group by C order by sum(F) desc limit 25", 1)

But it isn't. I get the error "CANNOT_GROUP_WITHOUT_AGG". I've found that to make this work, I need to select sum(F) as well, like so:

=QUERY('Raw Data'!A:Q, "Select C, sum(F) where Q = 'foo' and C != '' group by C order by sum(F) desc limit 25", 1)

First of all, why is this? The documentation (found here) states that "If you use a group by clause, then every column listed in the select clause must either be listed in the group by clause, or be wrapped by an aggregation function." I don't see how the first formula doesn't obey this criterion; every column in select (i.e. only C) is listed in the group by clause.

Second of all, how can I best modify this formula (if I can at all) to be able to only select C? If I'm not doing anything wrong (doubtful, but possible), is there a workaround for this, or do I have to just deal with it?

This question, and others like it, don't answer my question - I understand that if I want to select multiple columns, as I do in the second formula given above, I need to provide an aggregation function so that it's clear how they are to be grouped. In this case, though, there's only one column - I just want it (and only it) to be grouped. What am I missing here that doesn't allow for this?

Answer 1

The easiest solution is probably to wrap another QUERY formula around the first one:

=QUERY(QUERY('Raw Data'!A:Q, "select C, sum(F) where Q = 'foo' and C != '' group by C order by sum(F) desc limit 25", 1),"select Col1")