ORA-00937: it is not a group function on only one grouping

Question

I have to retrieve some data (Oracle RDBMS). I have to filter them, to grup them. So i thought about doing three nested query.
Starting from the outside in: a query to restrict a query to sort them and a query for selection (which also filter and groups them). This is the query:

    @SqlQuery("select count(*) personCount, SURNAME surname, SKILL skill, ROWNUM " +
        "     from (select * " +
        "        from (select count(*) personCount, SURNAME surname, SKILL skill from people " +
        "           where ....my filters....
        "          group by SURNAME, SKILL ) " +
        "       order by personCount DESC )  " +
        "     where ROWNUM \\<= :limit ")

But it gives me this error: ORA-00937: it is not a group function on only one grouping

Why?

Answer 1

You have to specify all the columns you select in the group by clause :

    @SqlQuery("select personCount, SURNAME surname, SKILL skill, ROWNUM " +
        "     from (select * " +
        "        from (select count(*) personCount, SURNAME surname, SKILL skill from people " +
        "           where ....my filters.... " +
        "          group by SURNAME, SKILL ) " +
        "       order by personCount DESC )  " +
        "     where ROWNUM \\<= :limit ")

Also, you already calculated the count of personCount, you don't need to count it again.

Note that I replaced the knowledge column with the skill column in the group by, I assumed it was a typo.