如何使用连接从两个不同的表检索两个聚合列?

Question

我想从ITEMS TABLE中检索Price Column的总和，并将其与GOODS TABLE连接。问题是当我尝试连接这些表时，货物表中的数量列gQnty的总和为DISH A给出了84而不是14。谁能帮助我如何实现这个任务，我可以得到确切的数量gQnty和价格iPrice与相同的查询。

表货物

gID | gName | gQnty
-------------------------
1     DISH A   10
2     DISH B   12
3     DISH A   4

表项目:

iID | gID | iItem | iPrice
--------------------------
1      1    Sugar    200
2      1    Milk     300
3      1    Fruits   100
4      2    Oil      200
5      3    Sugar    100
6      3    Milk     50
7      3    Fruits   40

我查询:

select g.gName, sum(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
from goods g join items i
on g.gID = i.gID
group by g.gName

预期结果:

gName  | gQnty | iPrice
----------------------------
DISH A |  14   |  790  
DISH B |  12   |  100

Answer 1

左连接是个好主意

WITH A AS (
SELECT gName, SUM(ITEMS.iPrice) AS iPrice
FROM GOODS
LEFT JOIN ITEMS
ON GOODS.gID = ITEMS.gID
GROUP BY gName 
)
SELECT gName, 
(SELECT SUM(gQnty) FROM GOODS WHERE GOODS.gName = A.gName GROUP BY gName ) AS gQnty,
iPrice
FROM A

dbfiddle

Answer 2

试试这个

select g.gName, max(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
from goods g join items i
on g.gID = i.gID
group by g.gName

或者

select g.gName, avg(g.gQnty)[gQnty], sum(i.iPrice)[iPrice]
from goods g join items i
on g.gID = i.gID
group by g.gName