Join two similar queries made to the same table in PostgreSQL

user_10263345 0
Viewed 123

Good evening, I have generated two queries to a PostgreSQL database, which are the following:

SELECT tarea, count(estado) AS "TotalCompletos" from tabla WHERE estado = 'Completo' GROUP BY  tarea;

Query_1_result.png

SELECT tarea, count(estado) AS "TotalIncompletos" from tabla WHERE estado = 'Incompleto' GROUP BY  tarea;

Query_2_result.png

My question is, how can I merge the above two queries into one query in order to produce a result similar to the following?

Expected_result.png

I have used UNION but I do not get the expected results. Attached is some sample code from the database I am using: Demo_DB

postgresql database
1 Answers
with q1 as (
SELECT tarea, count(estado) AS "TotalCompletos" from tabla WHERE estado = 'Completo' GROUP BY  tarea
),
q2 as (
SELECT tarea, count(estado) AS "TotalIncompletos" from tabla WHERE estado = 'Incompleto' GROUP BY  tarea
)
select tarea, coalesce("TotalCompletos", 0) as "TotalCompletos", coalesce("TotalIncompletos", 0) as "TotalIncompletos"
from q1 full join q2 using (tarea);
AdamKG_163610
Related