Several SQL syntax questions

syntax

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.

Best Answer

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);
Related Question