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