How to retrieve two aggregate columns from two different tables using joins?-CodePudding

I want to retrieve sum of Price Column from ITEMS TABLE and join it with the GOODS TABLE. The problem is when I try to join these tables, the sum of quantity column gQnty in Goods Table gives me 84 instead of 14 for DISH A. Can anyone help me how to achieve this task where I can get exact quantity gQnty and price iPrice with a same query.

TABLE GOODS

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

TABLE ITEMS:

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

My Query:

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

Expected Result:

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

CodePudding user response：

LEFT JOIN WOULD BE A GOOD IDEA

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

CodePudding user response：

Try this

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

or even

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