SQL Oracle group function problems

Question

I have problems understanding how to group with multiple tables, if i were to have a single table i can make it work, but not with two this is my problem bellow:

SELECT oc.custID, oc.custfirstname, oc.tankcap, oc.housetype, 
MIN(AVG(oh.fall)) FROM oil_cust oc, oil_houset oh GROUP BY oh.fall;

And this is the output that i get:

SELECT oc.custID, oc.custfirstname, oc.tankcap, oc.housetype,
       *
ERROR at line 1:
ORA-00937: not a single-group group function

I tried to have another group function for both tables but nothing seems to be working.

Hi, you have a Cartesian join here, there is nothing to link the two tables together. Is this correct? — Ben
– Ben, Commented Oct 13, 2013 at 17:25

David Aldridge · Accepted Answer · 2013-10-13 16:15:50Z

1

You're grouping by the column that you are aggregating on -- the exact opposite of what you should be doing.

answered Oct 13, 2013 at 16:15

David Aldridge

52.5k8 gold badges73 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Taryn Over a year ago

Not only that but they are also nesting aggregate functions and unless my memory is failing me, that it not allowed in any RDBMS.

David Aldridge Over a year ago

Actually you can as long as you do not have other non-aggregate columns. The group by applies to the inner function, and the outer function then executes without a group by so you can calculate the average of departmental salary maxima, for example. In this case if the minimum average "fall" was required then the group by can stay but the other non-aggregate columns would have to go.

Taryn Over a year ago

Huh, that is interesting. I guess I will have to perform some testing on it. Thanks for the info.

ypercubeᵀᴹ Over a year ago

@DavidAldridge That (the nesting of aggregate functions) is non ISO/ANSI, right? I haven't seen it before and I couldn't make it work in Postgres or SQL-Server.

David Aldridge Over a year ago

@ypercube No idea ... It might be ANSI and not supported by others, or non-ANSI and the same.

Collectives™ on Stack Overflow

SQL Oracle group function problems

1 Answer 1

5 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Related