Postgres Convert Rows to Columns based on a Common field Using Crosstab

Question

I have query select dist_name::text,service_name::text,status,count from mpr_application_status that returns

+---------------+--------------+----------------+-------+
| District Name | Service Name | Current Status | Count |
+---------------+--------------+----------------+-------+
| Dehradun      |          143 | APPROVED       |     1 |
| Dehradun      |         143  | PENDING        |     2 |
+---------------+--------------+----------------+-------+

I want to return a result set like this where we are converting rows into column based on the district name

+---------------+--------------+---------------+----------------+----------------+-------------+
| District Name | Service Name | ApprovedCount | Rejected Count | Pending  Count | Total Count |
+---------------+--------------+---------------+----------------+----------------+-------------+
| Dehradun      |          143 |             1 |              0 |              2 |           3 |
+---------------+--------------+---------------+----------------+----------------+-------------+

I have tried using crosstab function to return this desired table

SELECT *
FROM   crosstab(
   'select dist_name::text,service_name::text,count from mpr_application_status ORDER BY 1,2') 
   AS ct ("District Name" text, "Service Name" text,"Approved Count" bigint,"Pending Count" bigint, "Reject Count" bigint);

But encountering errors

ERROR:  return and sql tuple descriptions are incompatible
SQL state: 42601

Is there any way I can achieve this result on the Database end ? I am using postgresql 10

Philipp Johannis · Accepted Answer · 2020-10-29 09:05:35Z

3

I think you can do this by using SUM and CASE WHEN:

SELECT dist_name::text,service_name::text
,SUM(case when status = 'APPROVED' then count else 0 end) approved_count
,SUM(case when status = 'REJECTED' then count else 0 end) rejected_count
,SUM(case when status = 'PENDING' then count else 0 end) pending_count
,SUM(count) as total_count
FROM mpr_application_status
GROUP BY dist_name,service_name

answered Oct 29, 2020 at 9:05

Philipp Johannis

2,9661 gold badge20 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user330315 Over a year ago

Alternatively: count(*) filter (where status = 'APPROVED') as approved_count

Akhilesh Mishra · Accepted Answer · 2020-10-29 09:47:14Z

1

The format of crosstab() you are using will not comply with your desired output. You should write it like below:

SELECT 
"District Name",
"Service Name",
coalesce("Approved Count",0),
coalesce("Pending Count",0),
coalesce("Reject Count",0),
coalesce("Approved Count",0)+coalesce("Pending Count",0)+coalesce("Reject Count",0) "Total"
FROM   
crosstab(
'select dist_name::text "District Name", service_name::text "Service Name", status ,count 
from mpr_application_status ORDER BY 1,2',
'select ''APPROVED'' union select ''PENDING'' union select ''REJECTED'' ') 
AS ct ("District Name" text, "Service Name" text,"Approved Count" bigint,"Pending Count" bigint, "Reject Count" bigint);

You can do it using filter clause also

select 
dist_name,
service_name,
sum("count") filter (where status='Approved') "Approved Count",
sum("count") filter (where status='Pending') "Pending Count",
sum("count") filter (where status='Rejected') "Rejected Count",
sum("count")

from mpr_application_status
group by 1,2

DEMO

edited Oct 29, 2020 at 9:47

answered Oct 29, 2020 at 9:24

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

4 Comments

Rajeev Massey Over a year ago

ERROR: invalid return type DETAIL: Query-specified return tuple has 5 columns but crosstab returns 4. SQL state: 42601

Akhilesh Mishra Over a year ago

@RajeevMassey Please read the note as i have mentioned use 'select ''APPROVED'' union select ''PENDING'' union select ''REJECTED'' ' in place of 'select distinct status from mpr_application_status order by 1' because your sample data is not having Rejected data so current query is returning only 2 values Approved and Pending.

Rajeev Massey Over a year ago

if any of the APPROVED, REJECTED OR PENDING is not present it should by default put 0

Akhilesh Mishra Over a year ago

I have updated the answer as per note with DEMO and removed the note now check it.

Collectives™ on Stack Overflow

Postgres Convert Rows to Columns based on a Common field Using Crosstab

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related