BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Server Derived Tables

Some result sets are difficult to extract from a database without performing multiple queries or using temporary tables and cursors, which can harm performance. Derived tables help with some problems by creating virtual tables within the scope of a query.

Bringing the Queries Together

In the main query we will produce a list of salespeople. For each salesperson we'll show the number of sales that they made and the total value thereof. In the same row we'll include the number and total value of sales for the area code in which the salesperson operates. We'll also perform two calculations to determine the percentage of sales and sales value for each salesperson.

You can see the full query and its results below. Firstly we specify the seven columns that will be returned by the query, including the two calculated columns. Next, the FROM clause adds the two derived tables, each using one of the queries we've already seen above. Finally, the inner join links the two derived tables together where the area codes match.

NB: The results in the comment have shortened column names to allow you to see all of the results without scrolling.

SELECT
    Salesperson,
    NumSalesPersonSales,
    NumAreaSales,
    100 * NumSalesPersonSales / NumAreaSales AS NumSalesPercent,
    TotalSalesPersonSales,
    TotalAreaSales,
    100 * TotalSalesPersonSales / TotalAreaSales AS NumSalesPercent
FROM
(   SELECT
        Area,
        COUNT(Value) AS NumAreaSales,
        SUM(Value) AS TotalAreaSales
    FROM
        Sales
    GROUP BY
        Area) AS DerivedAreaSales
INNER JOIN
(   SELECT
        Salesperson,
        Area,
        COUNT(Value) AS NumSalesPersonSales,
        SUM(Value) AS TotalSalesPersonSales
    FROM
        Sales
    GROUP BY
        SalesPerson, Area) AS DerivedSalesPersonSales
    ON
        DerivedAreaSales.Area = DerivedSalesPersonSales.Area

/* RESULTS

Sperson NumSales AreaSales NSPercent TotalSales TotalAreaSales NumSalesPercent
------- -------- --------- --------- ---------- -------------- ---------------
Bob      2       3         66        225.00     345.00         65.2173
Sam      1       3         33        120.00     345.00         34.7826
Jim      1       2         50        120.00     250.00         48.00
Tim      1       2         50        130.00     250.00         52.00

*/
14 June 2012