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.
GROUP BY and UNION From Two Tables
Moderator: General Moderators
Re: GROUP BY and UNION From Two Tables
You need to group on a derived table from the union:
Code: Select all
SELECT density,SUM(weight) FROM (
(SELECT density , weight FROM inventory)
UNION ALL
(SELECT density, weight FROM `order`)
) AS a
GROUP BY density
ORDER BY densityRe: GROUP BY and UNION From Two Tables
ullasvk wrote: "Thank you pytrin"
"Thank you so much for that"
This has been driving me nuts for the past week, it worked like a charm.
Thanks and regards
Re: GROUP BY and UNION From Two Tables
Related topic: MySQL Query Optimization, Interpreting EXPLAIN Results
Edit: This post was recovered from search engine cache.
Edit: This post was recovered from search engine cache.