GROUP BY and UNION From Two Tables
Posted: Sat Apr 18, 2009 8:52 am
Hi Friends, i tried a lot for the solution but all in vain .please any one help me.
I have two tables Order and Inventory. Both have common fields i.e.,
ORDER table
Density Weight
1 23.2
1 22.4
1 8.6
2 9.5
2 18
3 32
3 5.6
3 9.9
3 5.3
INVENTORY Table
Density Weight
1 11.8
1 16.2
2 9.5
2 18
2 3.3
3 6.9
3 1.2
3 36.5
I want Query which retrieves sum of weight from both tables in the following manner
Density Weight
1 82.2
2 58.3
3 97.4
I tried a lot using GROUP BY,ORDER BY and UNION but the result look like this
"SELECT INVENTORY.DENSITY AS DENSITY , SUM(weight) from INVENTORY GROUP BY DENSITY UNION SELECT ORDER_DETAIL.DENSITY AS DENSITY,SUM(weight) from ORDER_DETAIL GROUP BY DENSITY ORDER BY DENSITY"
Density Weight
1 54.2[ORDER]
1 28[INVENTORY]
2 27.5[ORDER]
2 30.8[INVENTORY]
3 52.8[ORDER]
3 44.6[INVENTORY]
please give me any solution.
Thanks In Advance.
I have two tables Order and Inventory. Both have common fields i.e.,
ORDER table
Density Weight
1 23.2
1 22.4
1 8.6
2 9.5
2 18
3 32
3 5.6
3 9.9
3 5.3
INVENTORY Table
Density Weight
1 11.8
1 16.2
2 9.5
2 18
2 3.3
3 6.9
3 1.2
3 36.5
I want Query which retrieves sum of weight from both tables in the following manner
Density Weight
1 82.2
2 58.3
3 97.4
I tried a lot using GROUP BY,ORDER BY and UNION but the result look like this
"SELECT INVENTORY.DENSITY AS DENSITY , SUM(weight) from INVENTORY GROUP BY DENSITY UNION SELECT ORDER_DETAIL.DENSITY AS DENSITY,SUM(weight) from ORDER_DETAIL GROUP BY DENSITY ORDER BY DENSITY"
Density Weight
1 54.2[ORDER]
1 28[INVENTORY]
2 27.5[ORDER]
2 30.8[INVENTORY]
3 52.8[ORDER]
3 44.6[INVENTORY]
please give me any solution.
Thanks In Advance.