Several SQL syntax questions

Question

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) > (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.

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. — , Nov 24 '16 at 15:57
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 — shinzou, Nov 24 '16 at 16:06
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. — ypercubeᵀᴹ, Nov 24 '16 at 16:11
@ypercubeᵀᴹ: 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 — , Nov 24 '16 at 16:59

score 2 · Accepted Answer · 2016-11-24T16:08:37.020

2

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

edited Nov 24 '16 at 16:08

answered Nov 24 '16 at 16:07

Not use natural join? Why? It makes things a lot more simple. – shinzou Nov 24 '16 at 16:08
1

@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. – Nov 24 '16 at 16:09
1

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ᵀᴹ Nov 24 '16 at 16:13
1

About the natural join: You can natural join two tables even if they have no common column. It becomes a cross join. – ypercubeᵀᴹ Nov 24 '16 at 16:16
@ypercubeᵀᴹ: even worse then ;) – Nov 24 '16 at 16:56

Several SQL syntax questions

1 Answers1