Temp Table - Part 2 - Derived Tables

Derived table:

Example of using Derived table

Assume you have a table daily_sales and suppose we want to produce ranking of the sum of sales of products .

As we know we cannot use OLAP and aggregate functions together in a single query.

Hence we use a derived table as shown in the below query. The derived table contains the sum of sales per product and then the outer query uses the this result for performing ranking.

Select prodid,RANK(sumsales) from

(Select product_id, sum(sales) from

daily_sales group by product_id) AS D_TABLE(prodid,sumsales);

In the above query the derived table name is D_TABLE.

For derived table the select query should be within the parentheses following the FROM clause.

We can optionally provide names to the columns in the derived table. In the above example we named the derived columns as prodid and sumsales.