database - Join two similar queries made to the same table in PostgreSQL

Question

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

question from:https://stackoverflow.com/questions/65894454/join-two-similar-queries-made-to-the-same-table-in-postgresql

Answer 1

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);