O'Reilly logo

PostgreSQL Administration Cookbook, 9.5/9.6 Edition by Gabriele Bartolini, Gianni Ciolli, Simon Riggs

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required