Search code examples
sqloracle-databaseoracle12c

Subquery requiring group by without single row function

From my understanding queries that rely on one or more aggregate functions as well as at least one single row function require the single row functions to be placed in a group by clause, which makes sense overall.

enter image description here However I'm working through problems in an online resource and ran into the question in the picture, my logic behind why I answered it executes successfully but gives improper output is that the subquery is a query that has only an aggregate function, leaving me to believe that it requires no group by, why is it that this requires a group by in the subquery?

Solution

  • Already cleared by Gordon, "Nested aggregate requires a GROUP BY clause". If we consider the query into 2 parts, first part works fine if Having is given with specific value.

    Example:

    Run Queries in this link: https://livesql.oracle.com/apex/f?p=590:1:104596775146183::NO:RP::

    select count(*), PROD_CATEGORY_ID from SH.PRODUCTS group by PROD_CATEGORY_ID      
having count(*)>15;

    But we get error if we combine 2 aggregate functions,

    select max(count(PROD_CATEGORY_ID)) from SH.PRODUCTS ; --> Throws ORA-00978
select max(count(PROD_CATEGORY_ID)) from SH.PRODUCTS 
group by PROD_CATEGORY_ID; -->Gives max count of prod_cat

    Gives final result: 

    select count(*), PROD_CATEGORY_ID from SH.PRODUCTS 
group by PROD_CATEGORY_ID 
having count(*)=(select max(count(*)) from SH.PRODUCTS group by PROD_CATEGORY_ID);

    Good Examples in link: https://mahtodeepak05.wordpress.com/2014/12/17/aggregate-function-nesting-in-oracle/