Cross Tab With Totals, PostgreSQL

Cross Tab With Totals

DROP TABLE sales;
CREATE TABLE sales(
   id serial,
   country VARCHAR(3),
   type VARCHAR(15),
   month int,
   amount numeric(10,2)
);
 
INSERT INTO sales(country,type,month,amount)
VALUES ('PEN','Course',1,16),('PEN','Course',2,12),('PEN','Course',3,24),
       ('PEN','Subscription',4,30),('PEN','Subscription',5,30),('PEN','Subscription',6,30),
       ('PEN','Course',7,16),('PEN','Subscription',8,30),('PEN','Course',9,16),
       ('PEN','Subscription',10,30),('PEN','Subscription',11,30),('PEN','Course',12,12),
       ('COL','Subscription',1,30),('COL','Course',4,24),('COL','Subscription',6,30),
       ('COL','Subscription',12,30),('BOL','Course',1,12),('BOL','Course',3,12),
       ('MXN','Course',5,16);
 
CREATE EXTENSION IF NOT EXISTS tablefunc;

WITH t AS (
    SELECT split_part(key,'-',1) AS country, split_part(key,'-',2) AS type, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
        FROM crosstab(
          'SELECT CONCAT(country,''-'',type) AS key, month, SUM(amount) FROM sales GROUP BY key, month ORDER BY 1,2',
          'SELECT month FROM generate_series(1,12) AS month'
    ) AS (
        key text,
        Jan NUMERIC(10,2),
        Feb NUMERIC(10,2),
        Mar NUMERIC(10,2),
        Apr NUMERIC(10,2),
        May NUMERIC(10,2),
        Jun NUMERIC(10,2),
        Jul NUMERIC(10,2),
        Aug NUMERIC(10,2),
        Sep NUMERIC(10,2),
        Oct NUMERIC(10,2),
        Nov NUMERIC(10,2),
        Dec NUMERIC(10,2)
    )
)
SELECT
    *,
    COALESCE(jan,0)+
    COALESCE(feb,0)+
    COALESCE(mar,0)+
    COALESCE(apr,0)+
    COALESCE(may,0)+
    COALESCE(jun,0)+
    COALESCE(jul,0)+
    COALESCE(aug,0)+
    COALESCE(sep,0)+
    COALESCE(oct,0)+
    COALESCE(nov,0)+
    COALESCE(dec,0) total_country_type
FROM t
UNION ALL
SELECT '' Country, 'total' "type",
SUM(jan),
SUM(feb),
SUM(mar),
SUM(apr),
SUM(may),
SUM(jun),
SUM(jul),
SUM(aug),
SUM(sep),
SUM(oct),
SUM(nov),
SUM(dec),
SUM(
    COALESCE(jan,0)+
    COALESCE(feb,0)+
    COALESCE(mar,0)+
    COALESCE(apr,0)+
    COALESCE(may,0)+
    COALESCE(jun,0)+
    COALESCE(jul,0)+
    COALESCE(aug,0)+
    COALESCE(sep,0)+
    COALESCE(oct,0)+
    COALESCE(nov,0)+
    COALESCE(dec,0)
) total_country
FROM t


run \| edit \| history \| help	0