PostgreSQL 10 Administration Cookbook - Fourth Edition

How to do it…

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, ...