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 with 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 quarterly profit for nonlocal sales from all shops, as shown next:
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_profitFROM (SELECT * FROM salespoint ORDER BY sp_name) AS shopLEFT JOIN (SELECTspoint_id,sum(sale_price) - sum(cost) AS profit,count(*) AS nr_of_sales ...
