Oracle Sql group function is not allowed here-CodePudding

I need someone who can explain me about "group function is not allowed here" because I don't understand it and I would like to understand it.

I have to get the product name and the unit price of the products that have a price above the average

I initially tried to use this, but oracle quickly told me that it was wrong.

SELECT productname,unitprice
FROM products
WHERE unitprice>(AVG(unitprice));

search for information and found that I could get it this way:

SELECT productname,unitprice FROM products
WHERE unitprice > (SELECT AVG(unitprice) FROM products);

What I want to know is why do you put two select?

What does group function is not allowed here mean? More than once I have encountered this error and I would like to be able to understand what to do when it appears

Thank you very much for your time

CodePudding user response：

The phrase "group function not allowed here" is referring to anything that is in some way an "aggregation" of data, eg SUM, MIN, MAX, etc et. These functions must operate on a set of rows, and to operate on a set of rows you need to do a SELECT statement. (I'm leaving out UPDATE/DELETE here)

If this was not the case, you would end up with ambiguities, for example, lets say we allowed this:

select *
from   products
where  region = 'USA'
and    avg(price) > 10

Does this mean you want the average prices across all products, or just the average price for those products in the USA? The syntax is no longer deterministic.

CodePudding user response：

Here's another option:

SELECT *
  FROM (
       SELECT productname,unitprice,AVG(unitprice) OVER (PARTITION BY 1) avg_price
         FROM products)      
 WHERE unitprice > avg_price

The reason your original SQL doesn't work is because you didn't tell Oracle how to compute the average. What table should it find it in? What rows should it include? What, if any, grouping do you wish to apply? None of that is communicated with "WHERE unitprice>(AVG(unitprice))".

Now, as a human, I can make a pretty educated guess that you intend the averaging to happen over the same set of rows you select from the main query, with the same granularity (no grouping). We can accomplish that either by using a sub-query to make a second pass on the table, as your second SQL did, or the newer windowing capabilities of aggregate functions to internally make a second pass on your query block results, as I did in my answer. Using the OVER clause, you can tell Oracle exactly what rows to include (ROWS BETWEEN ...) and how to group it (PARTITION BY...).