连接对PostgreSQL中同一个表的两个类似查询

Question

晚上好，我已经生成了两个对PostgreSQL数据库的查询，它们是:

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

我的问题是，如何将上述两个查询合并为一个查询，以产生类似于以下的结果?

Expected_result.png

我已经使用了UNION，但我没有得到预期的结果。附件是我使用的数据库中的一些示例代码:Demo_DB

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