Home > Articles > Data > SQL

  • Print
  • + Share This
From the author of

Subqueries in the FROM Clause

Let's look at a similar query using different data. To examine the number of sales orders each store has produced, you write a query like this:

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

stor_id orders_by_store 
------- --------------- 
6380    2
7066    2
7067    2
7131    3
7896    3
8042    4
(6 row(s) affected)

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

select stor_id, count(sonum) as orders_by_store
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. Some versions of Transact-SQL allow nested aggregates in the HAVING clause, and return the results you expect.

Adaptive Server Enterprise

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

What do you do if your SQL dialect does not support aggregates inside aggregates? Is there a way to find the store with the largest number of orders? Try a FROM subquery. It creates a "virtual view."

  • Create a query that returns one column, the value of orders for each store.

      select count(sonum) as orders_by_store
      from sales
      group by stor_id
    
      orders_by_store 
      --------------- 
      2
      2
      2
      3
      3
      4
      (6 row(s) affected)
  • Convert the query to a FROM clause subquery—a kind of on-the-fly view from which the outer query will pull data. Assign a table alias (here FROMsubq) to the subquery in the FROM clause.

       select FROMsubq.orders_by_store
       from (select count(sonum) as orders_by_store
          from sales
          group by stor_id) FROMsubq
      
      orders_by_store 
      --------------- 
      2
      2
      2
      3
      3
      4
      (6 row(s) affected)
  • Use the subquery "column" as an argument to an aggregate in the outer query.

      select max(FROMsubq.orders_by_store)
      from (select count(sonum) as orders_by_store
         from sales
         group by stor_id) FROMsubq
      
      ----------- 
      4
      (1 row(s) affected) 

    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 query's HAVING clause.

    select stor_id, count(sonum) as max_orders
    from sales
    group by stor_id
    having count(sonum) = 
        ( select max (FROMsubq.orders_by_store)
         from ( select count(sonum) as orders_by_store
             from sales
             group by stor_id ) FROMsubq )
    
    stor_id  max_orders 
    -------  ----------- 
    8042     4
    (1 row(s) affected)
  • + Share This
  • 🔖 Save To Your Account