使用子查询并加入
select a.group_id,a.amt as tableamnt,b.amt as table2amt from (
select group_id,sum(amount) as amt from table1 group by group_id
) a join (
select group_id,sum(amount) as amt from table2 group by group_id
) b on a.group_id=b.group_id
,
您需要什么:
SELECT t1.GROUP_ID as group_id,sum(t1.AMOUNT) as table_1_sum,sum(t2.amount) as table_2_sum
from Table1 t1
left join Table2 t2
on t1.id = t2.id
group by t1.GROUP_ID
order by t1.GROUP_ID;
这里是DEMO
干杯!
这是Zaynul Abadin Tuhin答案的正确版本:
select a.group_id,sum(amount) as amt from table1 group by group_id
) a join (
select group_id,sum(amount) as amt from table2 group by group_id
) b on a.group_id=b.group_id
order by group_id
这是第二个DEMO
,
您必须执行2个子查询以按group_id对数据进行分组,然后再加入2个子查询。像这样:
SELECT group_id,table_1_sum,table_2_sum from (
SELECT
group_id,SUM(amount) as table_1_sum
FROM
Table1
GROUP BY
group_id;
INNER JOIN
SELECT
group_id,SUM(amount) as table_2_sum
FROM
Table2
GROUP BY
group_id;
ON Table1.group_id = Table2.group_id
)
,
我将@Zaynul的查询修改为正确的,并且可以在Postgresql中使用。
您想通过对group_id分组来分别汇总每个表的数量。
因此,您不能直接使用JOIN查询。
您必须首先分别查询和汇总每个表。
然后加入先前查询的结果。
SQL语法将如下所示:
SELECT a.group_id,a.amt as table_1_sum,b.amt as table_2_sum FROM (
SELECT group_id,SUM(amount) as amt FROM table1 GROUP BY group_id
) a join (
SELECT group_id,SUM(amount) as amt FROM table2 GROUP BY group_id
) b ON a.group_id = b.group_id
我已经在pgAdmin4中进行了测试,并获得了想要的结果。
SumQueryResult
----------------------------------------
group_id table_1_sum table_2_sum
1 10 10(this can't be 100)
2 100 110
本文链接:https://www.f2er.com/3168406.html