Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

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

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:

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

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

>Solution :

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

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading