Single group function error in oracle

Question

SELECT name 
FROM instructor natural join teaches
WHERE course_id = (SELECT course_id 
                  FROM (SELECT course_id, MAX(mycount) 
                        FROM (SELECT course_id, count(ID) as mycount 
                              FROM takes GROUP BY course_id)));

This query doesn't work in oracle. It shows single group function error.

Please better tell what would you like to obtain and post what are your tables. This query is very por quality. — Kacper, Nov 03 '16 at 18:59
Possible duplicate of [SQL not a single-group group function](http://stackoverflow.com/questions/1795198/sql-not-a-single-group-group-function) — Alfabravo, Nov 03 '16 at 19:00

score 0 · Answer 1 · answered Nov 03 '16 at 19:00

Could be you obtain more then a result try in and missing a group by

SELECT name 
FROM instructor natural join teaches
WHERE course_id IN  (SELECT course_id 
              FROM (SELECT course_id, MAX(mycount) 
                    FROM (SELECT course_id, count(ID) as mycount 
                          FROM takes GROUP BY course_id) 
                    GROUP BY course_id));

score 0 · Answer 2 · answered Nov 03 '16 at 19:01

You're missing a group by. :)

SELECT name FROM instructor 
    natural join teaches 
    WHERE course_id = 
    (SELECT course_id FROM 
        (SELECT course_id, MAX(mycount) FROM 
            (SELECT course_id, count(ID) as mycount FROM takes GROUP BY course_id)
        group by course_ID) -- don't forget this one!
    );

Also, that outer subquery won't really do anything. You've already got one row per course_ID, so the max(mycount) will be exactly the same as mycount.

score 0 · Answer 3 · answered Nov 03 '16 at 19:02

lThe error occurs in your second level of subqueries:

(SELECT course_id, MAX(mycount) 
 FROM (SELECT course_id, count(ID) as mycount 
       FROM takes
       GROUP BY course_id)
)

You have an aggregation function but no GROUP BY.

I would also advise you to avoid NATURAL JOIN. This is just a bug waiting to happen. Hiding the keys used for the join is a bad thing, that makes queries harder to understand and more prone to error.

And then, your query is rather hard to follow. I would suggest that you ask another question, providing sample data and desired results. There is probably a simpler way to express this.

Single group function error in oracle

3 Answers3