here is the scenario.
i have 3 tables, like the following
Expand|Select|Wrap|Line Numbers
- [TABLE 1: PRODUCTS]
- [CODPROD],[ NAME]
- 1 , PROD1
- 2 , PROD2
- 3 , PROD3
- [END OF TABLE 1]
- [TABLE 2: SALES_X]
- [CODPROD], [AMT]
- 1 , 3
- 2 , 1
- 3 , 1
- 2 , 5
- 2 , 1
- 1 , 4
- [END OF TABLE 2]
- [TABLE 3: SALES_Y]
- [CODPROD], [AMT]
- 3 , 3
- 2 , 1
- 2 , 1
- 2 , 1
- 1 , 30
- [END OF TABLE 3]
Expand|Select|Wrap|Line Numbers
- sql result:
- [PRODUCTS.NAME],[SALES_X.AMT + SALES_Y.AMT]
- PROD1 , 37
- PROD2 , 10
- PROD3 , 4
I Have tried using SUM(), COUNT(), Joins, Unions, but still did not get how to do it right..
i'm trying with this, but its bugged
Expand|Select|Wrap|Line Numbers
- select a.name, SUM(b.amt) + SUM(c.amt) from PRODUCTS a
- LEFT JOIN SALES_X b
- ON b.codprod= a.codprod
- LEFT JOIN SALES_Y c
- on c.codprod= a.codprod
- GROUP BY A.NAME