Page 1 of 1

GROUP BY and UNION From Two Tables

Posted: Sat Apr 18, 2009 8:52 am
by ullasvk
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.

Re: GROUP BY and UNION From Two Tables

Posted: Sat Apr 18, 2009 12:27 pm
by Eran
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 density

Re: GROUP BY and UNION From Two Tables

Posted: Sat Apr 18, 2009 1:36 pm
by ullasvk
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

Posted: Sat Apr 18, 2009 2:47 pm
by McInfo
Related topic: MySQL Query Optimization, Interpreting EXPLAIN Results

Edit: This post was recovered from search engine cache.