Postgresql – Pivot add-on in cross-tab table Postgresql

pivotpostgresql

Given the schema here I wonder how I can use Pivot add-on to calculate a cross-tab table in the below format please?

Africa   America  Asia       Australia/Oceania   Europe     Total
#cities  #cities  #cities    #cities             #cities    #cities

The below script returns a normal result but I want it to appear in a cross-tab

SELECT  en.continent,count(ci.name)
FROM encompasses AS en 
INNER JOIN city AS ci ON en.country = ci.country 
GROUP BY en.continent
Union all
select 'Total', COUNT(ci.name)
from city AS ci;

The create/insert commands can be found here:

CREATE TABLE Country
(Name VARCHAR(35) NOT NULL UNIQUE,
 Code VARCHAR(4) CONSTRAINT CountryKey PRIMARY KEY,
 Capital VARCHAR(35),
 Province VARCHAR(35),
 Area NUMERIC CONSTRAINT CountryArea
   CHECK (Area >= 0),
 Population NUMERIC CONSTRAINT CountryPop
   CHECK (Population >= 0));

CREATE TABLE City
(Name VARCHAR(35),
 Country VARCHAR(4),
 Province VARCHAR(35),
 Population NUMERIC CONSTRAINT CityPop
   CHECK (Population >= 0),
 Longitude NUMERIC CONSTRAINT CityLon
   CHECK ((Longitude >= -180) AND (Longitude <= 180)) ,
 Latitude NUMERIC CONSTRAINT CityLat
   CHECK ((Latitude >= -90) AND (Latitude <= 90)) ,
 CONSTRAINT CityKey PRIMARY KEY (Name, Country, Province));

CREATE TABLE encompasses
(Country VARCHAR(4) NOT NULL,
 Continent VARCHAR(20) NOT NULL,
 Percentage NUMERIC,
   CHECK ((Percentage > 0) AND (Percentage <= 100)),
 CONSTRAINT EncompassesKey PRIMARY KEY (Country,Continent));


 INSERT INTO country VALUES ('Albania','AL','Tirane','Albania',28750,3249136);
 INSERT INTO country VALUES ('Greece','GR','Athens','Attiki',131940,10538594);
 INSERT INTO country VALUES ('Macedonia','MK','Skopje','Macedonia',25333,2104035);
 INSERT INTO country VALUES ('Serbia','SRB','Belgrade','Serbia',77474,7379339);
 INSERT INTO country VALUES ('Montenegro','MNE','Podgorica','Montenegro',14026,672180);
 INSERT INTO country VALUES ('Kosovo','KOS','Pristina','Kosovo',10887,1804838);
 INSERT INTO country VALUES ('Andorra','AND','Andorra la Vella','Andorra',450,72766);
 INSERT INTO country VALUES ('France','F','Paris','Ile de France',547030,58317450);
 INSERT INTO country VALUES ('Spain','E','Madrid','Madrid',504750,39181114);


 INSERT INTO encompasses VALUES ('LS','Africa',100);
 INSERT INTO encompasses VALUES ('RM','Africa',100);
 INSERT INTO encompasses VALUES ('MW','Africa',100);
 INSERT INTO encompasses VALUES ('MOC','Africa',100);
 INSERT INTO encompasses VALUES ('MS','Africa',100);
 INSERT INTO encompasses VALUES ('MAYO','Africa',100);
 INSERT INTO encompasses VALUES ('SD','Africa',100);
 INSERT INTO encompasses VALUES ('REUN','Australia/Oceania',100);
 INSERT INTO encompasses VALUES ('HELX','Australia/Oceania',100);
 INSERT INTO encompasses VALUES ('STP','Africa',100);
 INSERT INTO encompasses VALUES ('SY','Africa',100);


INSERT INTO city VALUES ('Tirane','AL','Albania',192000,19.8,41.3);
 INSERT INTO city VALUES ('Shkoder','AL','Albania',62000,19.2,42.2);
 INSERT INTO city VALUES ('Durres','AL','Albania',60000,19.3,41.3);
 INSERT INTO city VALUES ('Vlore','AL','Albania',56000,19.3,40.3);
 INSERT INTO city VALUES ('Elbasan','AL','Albania',53000,20.1,41.1);
 INSERT INTO city VALUES ('Korce','AL','Albania',52000,20.5,40.4);
 INSERT INTO city VALUES ('Komotini','GR','Anatoliki Makedhonia kai Thraki',NULL,NULL,NULL);
 INSERT INTO city VALUES ('Kavalla','GR','Anatoliki Makedhonia kai Thraki',56705,NULL,NULL);
 INSERT INTO city VALUES ('Athens','GR','Attiki',885737,23.7167,37.9667);
 INSERT INTO city VALUES ('Piraeus','GR','Attiki',196389,NULL,NULL);

It the sample data it should only include 2 continents Africa and Australia/Oceania.

Best Answer

caveats below

With tablefunc

First you need to add the tablefunc extension

CREATE EXTENSION tablefunc;

What you need is something like this,

SELECT *
FROM crosstab(
  $$
    SELECT 0, coalesce(continent, 'Total'), count(city.name)
    FROM encompasses AS e
    JOIN country ON e.country = country.code
    JOIN city ON country.code = city.country
    GROUP BY GROUPING SETS ((e.continent), ())
    ORDER BY 1, 2;
  $$,
  $$VALUES ('Africa'), ('America'), ('Asia'), ('Australia/Oceania'), ('Europe'), ('Total')$$
) AS t(rowid int, africa int, america int, asia int, australia_oceania int, europe int, total int);

Should get ya done.

Of course none of this works with the sample data because none of those countries have entires in encompasses,

INSERT INTO encompasses (country, continent) VALUES ('GR', 'Africa');

 rowid | africa | america | asia | australia_oceania | europe | total 
-------+--------+---------+------+-------------------+--------+-------
     0 |      4 |         |      |                   |        |     4
(1 row)

You may want to replace 

$$VALUES ('Africa'), ('America'), ('Asia'), ('Australia/Oceania'), ('Europe')$$

with

$$ SELECT DISTINCT continent FROM encompasses ORDER BY 1; $$

But either way, you'll have to type that out in the table definition (the list in the t(rowid int...))

Caveats

I don't actually do this in my code, ever. This pivot is a waste of time and computation. I would always do this in whatever language I'm using to interact with the db. I don't actually think this is a good idea (here). But this is how it's done.

Related Question