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


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?
Can you do a query inside a "from"?

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

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

Select x
From y
Group by x
Having avg(x) &gt; (select *
                 from *
                 where x &gt; 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.
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 &gt; (or &lt; 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) &gt; (select count(*) -- no idea what you would want to do here
                 from y
                 where x &gt; 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) &gt; ANY (select x -- still only ONE column allowed
                     from y
                     where x &gt; 1);