Trouble with GROUP BY, HAVING, and JOINS

Question

So the object of this query is to display the criminal's ID, last name and first name (all of which can be found in the "Criminals" table), and then to only display criminals who have more than one sentence -- which I did by using the HAVING clause that displays the condition that a criminal must have more than one sentence_id (the Sentence table primary key). When I typed in the following code however, I received an error that said "Columns that are used for a named-join (either a NATURAL join or a JOIN with a USING clause) cannot have an explicit qualifier.

SELECT c.criminal_id, c.last, c.first
 FROM criminals c JOIN sentences s USING (criminal_id)
 GROUP BY s.criminal_id
 HAVING COUNT(s.sentence_id) > 1;

I tried removing the qualifiers (which I understand to be the c. and s. which precede the column names), but then I got an error that said the query wasn't a GROUP BY expression. I'm pulling my hair out here trying to figure this thing out. If you can help, I'd really appreciate it. Thank you.

Answer 1

Nevermind! If anyone else sees this, I found the real error. I removed the qualifiers. The real problem was that when you select multiple columns while using a GROUP BY clause, you must GROUP BY ALL non-aggregate functions. So my correct code was this:

SELECT criminal_id, last, first
 FROM criminals JOIN sentences USING (criminal_id)
 GROUP BY criminal_id, last, first
 HAVING COUNT(sentence_id) > 1;

Hope this helps anyone else who stumbled onto this question.