Knex query error "pollers.description\" must appear in the GROUP BY clause or be used in an aggregate function"

Question

So I have 2 tables(pollers and errors) I am trying to join on where pollers.id = errors.poller_id and then count the number of errors a poller has using the pollers.id. I used:

 knex.raw(`select poller_name, description, count(pollers.id) as "poller_errors" 
from pollers
inner join 
errors on pollers.id = errors.poller_id 
group by poller_name`)

it came up with an error "pollers.description\" must appear in the GROUP BY clause or be used in an aggregate function"

Answer 1

The error seems pretty clear. As a general rule, the unaggregated columns in the select need to be in the group by. So just put them there:

select poller_name, description, count(pollers.id) as "poller_errors"
from pollers inner join
     errors
     on pollers.id = errors.poller_id
group by poller_name, description;

That said, there is one important exception to this, which goes by the fancy name of "functional dependency". You can aggregate by a primary or unique key and use other columns from the same table. I am guessing that pollers.id is a unique key. If so, you could write this as:

select p.poller_name, p.description, count(*) as poller_errors
from pollers p inner join
     errors e
     on p.id = e.poller_id
group by p.id;

Note the other changes to this code:

• All column references are qualified, so it is clear to you, the compiler, and anyone who reads the code where they are coming from.
• Table abbreviations are used for the aliases.
• The double quotes are removed from the column aliases. Don't get in the habit of escaping column references.