Several SQL syntax questions

Question

I have several small questions and I wrote some simple examples to clarify them:

1. Will a nested aggregate take one value from from all groups? Like the minimal maximum of all groups? or the minimum count?

   Select x, min(max(z))
   From y
   Group by x
   

   On the same note, is there any merit in doing the following to get the minimal count from all groups, or the second line is unnecessary?

   select x, min(count(*))
   select x, count(*)
   From y
   Group by x
   

   If both are invalid, how would you do a query on all groups like taking the minimum of all maximums in each group?

2. Can you do a query inside a "from"?

   Select x 
   From y natural inner join (select z AS y
    from foo)
   

3. Is it allowed to do from * after a group by?

   Select x
   From y
   Group by x
   Having avg(x) > (select *
    from *
    where x > 1)
   

   and if not, how would you do a query on each group after the group by?

Note: this isn't some live version of SQL server, just old theoretical SQL.

• user1822: The first query is invalid - you can't nest aggregates like that. The second one under 1) is also invalid - you can't have two select keywords without using a derived table or a sub-query. And 3) is a clear no.
• shinzou: So what is the proper way to do a query on all groups like taking the minimum from all maximums? And is it possible to have a predicate for each group after group by? @a_horse_with_no_name
• ypercubeᵀᴹ: The first query (Select x, min(max(z)) From y Group by x) is not valid SQL but it runs in Oracle (and only there) without error.
• user1822: @ypercubeTM: that will not run with Oracle. You can only nest aggregates if you don't select additional columns. e.g. select min(max(z)) from y group by x does indeed work in Oracle, but not the query shown
• ypercubeᵀᴹ: @a_horse_with_no_name ah yes, true.

Answer 1

re 1)

With standard SQL the only way to do that is:

select min(cnt)
from (
 select x, count(*) as cnt
 from y
 group by x
) t

re 2)

yes you can join to a query, but you need to give the derived table an alias

Select x 
From y 
 natural join (select z AS y
 from foo) as t;

That assumes that the table y also has a column y - otherwise there wouldn't be two identical columns that the natural join could use.

But in general you should avoid the natural join. Use an explicit join instead:

select x 
from y 
 join (select z AS y
 from foo
 ) as t on t.y = y.id;

re 3)

No, from * is never allowed. But I have no clue what you intend to do with that. The sub-select used with > (or < or =) has to return exactly one row and exactly one column so you would need something like:

Select x
From y
Group by x
Having avg(x) > (select count(*) -- no idea what you would want to do here
 from y
 where x > 1);

If the subselect returns more then one row you would need to use ANY

Select x
From y
Group by x
Having avg(x) > ANY (select x -- still only ONE column allowed
 from y
 where x > 1);

• shinzou: Not use natural join? Why? It makes things a lot more simple.
• user1822: @kuhaku: natural join just looks at column names and it picks the first ones that match. You have no control over it. If you are learning SQL, believe me: stay away from it.
• ypercubeᵀᴹ: The first query can also be written without derived table: select count(*) as cnt from y group by x order by cnt desc fetch first 1 row only;
• ypercubeᵀᴹ: About the natural join: You can natural join two tables even if they have no common column. It becomes a cross join.
• user1822: @ypercubeTM: even worse then ;)