Regarding to Terry's request on multiple aggregates and
Shahbaz's request for generating a cross tab ( pivot table )
in September, I've found an excellent example on a german inet page
http://www.itrain.de/
I've translated it and think it's useful for many who subscribed
(I hope so, maybe it was told before, but I couldn't find
anything about this topic in the techdecs).
Objective:
There is a relation "sales",
holding the sales of different products of different vendors.
The task is to generate a report which shows the sales
of every vendor and every product.
Consider the following table populated with some data:
CREATE TABLE sales (
product TEXT,
vendor TEXT,
sales INTEGER
);
INSERT INTO sales VALUES ( 'milk' , 'mr. pink' , 12 ) ;
INSERT INTO sales VALUES ( 'milk' , 'mr. brown' , 8 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. green' , 2 ) ;
INSERT INTO sales VALUES ( 'milk' , 'mr. green' , 34 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. pink' , 17 ) ;
INSERT INTO sales VALUES ( 'butter', 'mr. brown' , 2 ) ;
INSERT INTO sales VALUES ( 'honey' , 'mr. pink' , 19 ) ;
The following query generates the report:
SELECT product,
SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr.
pink ",
SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr.
brown",
SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr.
green",
SUM(sales) AS "sum of sales"
FROM sales GROUP BY product ;
product | mr. pink | mr. brown | mr. green | sum of sales
---------+-----------+-----------+-----------+--------------
butter | 17 | 2 | 0 | 19
honey | 19 | 0 | 2 | 21
milk | 12 | 8 | 34 | 54
(3 rows)
The example is based on MS SQL Server 7.0 and it appears to be
there is a valuable feature called CUBE which completes the report.
SELECT CASE WHEN GROUPING(product) = 1 THEN 'sum of sales' ELSE product
END,
SUM(CASE vendor WHEN 'mr. pink' THEN sales ELSE 0 END) AS "mr.
pink ",
SUM(CASE vendor WHEN 'mr. brown' THEN sales ELSE 0 END) AS "mr.
brown",
SUM(CASE vendor WHEN 'mr. green' THEN sales ELSE 0 END) AS "mr.
green",
SUM(sales) AS "sum of sales"
FROM sales GROUP BY product WITH CUBE ;
product | mr. pink | mr. brown | mr. green | sum of sales
--------------+-----------+-----------+-----------+--------------
butter | 17 | 2 | 0 | 19
honey | 19 | 0 | 2 | 21
milk | 12 | 8 | 34 | 54
sum of sales | 48 | 10 | 36 | 94
(4 rows)
I would like to hear from the core team whether they think this feature
is worthy to be implemented, or even better, is there a similar one or
an easy workaround already.
It's obvious this approach is most inflexible.
As soon as there is a new vendor, one has to re-write the query and add
SUM(CASE vendor WHEN 'mr. new' THEN ... ,
In an advanced example it is shown how to deal with cross tabs in
general
using a stored procedure. I am going to translate this and re-write it
for postgres, too (ok, I will try).
Regards, Christoph
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
