Pivot add-on in cross-tab table Postgresql

Question

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.

Answer 1

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.