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?

It seems that you are extracting SKILL without using it in GROUP BY clause — Aleksej, Mar 23 '16 at 12:25

J. Chomel · Answer 1 · 2016-03-23T12:50:06.363

You must not do the count in the external select:

   @SqlQuery("select personCount, surname, 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 ")

score 1 · Accepted Answer · answered Mar 23 '16 at 12:29

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.

Yes now it works. I was wrong to write in the code of my question. Thanks again! — panagulis72, Mar 23 '16 at 12:37

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

2 Answers2