Simplifying a query usually means restructuring it so that parts of it can be defined separately, and then used by other parts.
We'll illustrate the possibilities by rewriting the following query in several ways.
The complex query in our example case is a so-called pivot or cross-tab query. This query retrieves the quarterly profit for non-local sales from all shops, as shown in the following:
SELECT shop.sp_name AS shop_name, q1_nloc_profit.profit AS q1_profit, q2_nloc_profit.profit AS q2_profit, q3_nloc_profit.profit AS q3_profit, q4_nloc_profit.profit AS q4_profit, year_nloc_profit.profit AS year_profit FROM (SELECT * FROM salespoint ORDER BY sp_name) AS shop LEFT JOIN ( SELECT spoint_id, sum(sale_price) - sum(cost) AS profit, ...
