sql - 如何使查询选择语句结果像这样？

Question

我正在使用 postgre sql，我想像这样从数据库中选择数据

------------------------------------
total1 | total2 | total3 | province|
------------------------------------
1      |1       |2       |Maluku   |
2      |3       |4       |Aceh     |
4      |7       |2       |Riau     |
------------------------------------

但我的查询结果是这样的

------------------------------------
total1 | total2 | total3 | province|
------------------------------------
1      |1       |2       |Maluku   |
1      |1       |2       |Aceh     |
1      |1       |2       |Riau     |
------------------------------------

我的查询

SELECT (SELECT COALESCE(COUNT(id),0) FROM vent) as total1, 
       (SELECT COALESCE(COUNT(id),0) FROM ventWHERE jenis='Vent-I') as total2,
       (SELECT COALESCE(COUNT(id),0) FROM ventWHERE status='Terpakai') as total3, 
        b.provinsi as province 
FROM public."admin_provinsi" as b LEFT JOIN 
     public."rs" as a 
     on a.provno = b.idprov 
GROUP BY b.provinsi, b.idprov 
ORDER BY total1 DESC

如何做出这样的查询结果？

我的方案数据库表发泄

|id|jenis |id_rs|status  |
--------------------------
|1 |vent-i|1    |Terpakai|
|2 |vent-i|2    |Tidak   |

表rs

|gid|name     |provno|
-----------------
|1  |rs depok | 1    |
|1  |rs depok2| 1    |

表 admin_provinsi

|idprov|nama  |
---------------
|1     |Maluku|
|2     |Aceh  |
|3     |Riau  |

标签： sqldatabasepostgresql