Subqueries in the FROM Clause

Let's say you want to look at the number of sales orders each store has produced. You write a query like this:

select stor_id, count(sonum)
from sales
group by stor_id

stor_id count(sales.sonum)
======= ==================
7066                     2
7067                     2
7131                     3
8042                     4
6380                     2
7896                     3
[6 rows]

To see just the highest value, you might consider adding a HAVING clause with a nested aggregate:

select stor_id, count(sonum)
from sales
group by stor_id
having  count (sonum)  = max (count (sonum ) )
					

However, in most systems, you'll get an error message or no rows: Nested aggregates are prohibited.

stor_id
------- -----------
8042              4
(1 row affected)

So what do you do if you want to find the store with the largest number of orders? Try a FROM subquery. It creates a “virtual view” (see Chapter 9 for a discussion of views).

• Use a subquery in the outer FROM to define a subset of data with a single column.

• Assign column display labels (HiNum) and table aliases (s) to the FROM subquery.

• Use the subquery column as an argument to an aggregate in the outer query.

select max ( s.HiNum )
from  (select count(sonum) as HiNum
						       from sales
						group by stor_id) s
max(s.HiNum)
============
           4
[1 row]

To include store numbers in the results, create a new query with grouping by stores. Then move the original query (with its FROM subquery) to the outer HAVING clause.

select stor_id, count(*)
from sales
group by stor_id
having count(*) =
        ( select max (s.HiNum)
						         from  ( select count(*) as HiNum
						                 from sales
						                 group by stor_id ) s
         )
stor_id    count(*)
======= ===========
8042              4
[1 row]