CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
Jaen Yonten
Jaen Yonten

Reputation: 5

Postgres Crosstab Query To Count Statuses Per City

I try to run an sql query in Postgres that requires crosstab. I have never use crosstab queries before. My 3 tables are shown below:

locations TABLE:

location_id , lang_id, path

crm_statuses TABLE:

crm_status_id, crm_status_name

store_crm TABLE:

store_crm_id, status (references in crm_status_id of crm_statuses table), location_id (references in location_id of locations table)

I want to get the locations as columns from the locations table by joining the store_crm table or at least to write them hardcoded as they are only 3 (London,Manchester,Leeds). As rows I want to get the crm statuses. As content I want to count how many active, inactive and pending stores, each location has. The active,inactive,pending are my crm_statuses. The desired results table will have the following format. 

Status    London  Manchester Leeds
Active    2       4          5
Inactive  6       1          3
Pending   4       4          5

How can I achieve that?

Upvotes: 0

Views: 1387

Answers (1)

icuken
icuken

Reputation: 1356

You can hardcode cities as columns in a following query:

SELECT 
    * 
FROM crosstab('
        SELECT 
            cs.crm_status_name, 
            l.path, 
            COUNT(*)  
        FROM store_crm AS sc 
        JOIN locations AS l ON (sc.location_id=l.location_id) 
        JOIN crm_statuses AS cs ON (cs.crm_status_id=sc.status) 
        GROUP BY cs.crm_status_name, l.path 
        ORDER BY 1,2
    ', 
    '
        SELECT 
            path 
        FROM 
            locations
    ') AS f("Status" text, "London" text, "Manchester" text, "Leeds" text);

With a result:

  Status  | London | Manchester | Leeds 
----------+--------+------------+-------
 Active   | 1      | 2          | 1
 Inactive | 2      | 1          | 4
 Pending  |        | 1          | 1
(3 rows)

If you have database schema like that:

test=# \d+ locations
                                                    Table "public.locations"
   Column    |  Type   |                            Modifiers                            | Storage  | Stats target | Description 
-------------+---------+-----------------------------------------------------------------+----------+--------------+-------------
 location_id | integer | not null default nextval('locations_location_id_seq'::regclass) | plain    |              | 
 lang_id     | integer |                                                                 | plain    |              | 
 path        | text    |                                                                 | extended |              | 
Indexes:
    "locations_pkey" PRIMARY KEY, btree (location_id)
Referenced by:
    TABLE "store_crm" CONSTRAINT "store_crm_location_id_fkey" FOREIGN KEY (location_id) REFERENCES locations(location_id)

test=# \d+ crm_statuses
                                                       Table "public.crm_statuses"
     Column      |  Type   |                              Modifiers                               | Storage  | Stats target | Description 
-----------------+---------+----------------------------------------------------------------------+----------+--------------+-------------
 crm_status_id   | integer | not null default nextval('crm_statuses_crm_status_id_seq'::regclass) | plain    |              | 
 crm_status_name | text    |                                                                      | extended |              | 
Indexes:
    "crm_statuses_pkey" PRIMARY KEY, btree (crm_status_id)
Referenced by:
    TABLE "store_crm" CONSTRAINT "store_crm_status_fkey" FOREIGN KEY (status) REFERENCES crm_statuses(crm_status_id)

test=# \d+ store_crm
                                                     Table "public.store_crm"
    Column    |  Type   |                            Modifiers                             | Storage | Stats target | Description 
--------------+---------+------------------------------------------------------------------+---------+--------------+-------------
 store_crm_id | integer | not null default nextval('store_crm_store_crm_id_seq'::regclass) | plain   |              | 
 status       | integer |                                                                  | plain   |              | 
 location_id  | integer |                                                                  | plain   |              | 
Indexes:
    "store_crm_pkey" PRIMARY KEY, btree (store_crm_id)
Foreign-key constraints:
    "store_crm_location_id_fkey" FOREIGN KEY (location_id) REFERENCES locations(location_id)
    "store_crm_status_fkey" FOREIGN KEY (status) REFERENCES crm_statuses(crm_status_id)

Upvotes: 1

Related Questions