CODEWITHSUNDEEP
mysqlleft-joinunion
klye_g
klye_g

Reputation: 1242

MYSQL Union and combine amounts from duplicate rows

I am trying to combine results from two different set of tables into one result. Here is my query so far:

SELECT * 
FROM ( SELECT product_table1.product_id as product_id, sum(product_table1.qty) as quantity, sum(product_table1.paid) as amount  
FROM product_table1
LEFT JOIN table2 ON table2.id = product_table1.table2_id
WHERE product_table1.product_id IN ( SELECT id FROM products_table WHERE active = 'yes' )
AND table2.active = 'yes'
GROUP BY product_id

UNION 

SELECT product_table2.product_id as product_id, sum(product_table2.qty) as quantity, sum(product_table2.paid) as amount 
FROM product_table2
LEFT JOIN table3 ON table3.id = product_table2.table3_id
WHERE product_table2.product_id IN ( SELECT id FROM products_table WHERE active = 'yes' )
AND table3.active = 'yes'
GROUP BY product_id

) AS product_sales

And here is how the data is coming back:

product_id | quantity | amount
1            100        200
2            200        300
3            300        600
1            500        700
4            200        200

I am trying to figure out how best to take these two set of numbers (each having the same amount of columns and data types), and combining them into one set of results without having to bring php into it. Is this possible?

I found some other solutions but am not sure if they match what I am trying to do. Example of another solution that I think is what I need, but not to sure..

Upvotes: 1

Views: 2946

Answers (2)

peterm
peterm

Reputation: 92805

Use GROUP BY in your outer SELECT and UNION ALL instead of UNION

SELECT product_id,  
       SUM(quantity) as quantity,
       SUM(amount) as amount
  FROM ( 
    SELECT product_table1.product_id as product_id, 
           SUM(product_table1.qty) as quantity, 
           SUM(product_table1.paid) as amount  
      FROM product_table1
      LEFT JOIN table2 ON table2.id = product_table1.table2_id
     WHERE product_table1.product_id IN ( SELECT id FROM products_table WHERE active = 'yes' )
       AND table2.active = 'yes'
     GROUP BY product_id
     UNION ALL
    SELECT product_table2.product_id as product_id, 
           SUM(product_table2.qty) as quantity, 
           SUM(product_table2.paid) as amount 
      FROM product_table2
      LEFT JOIN table3 ON table3.id = product_table2.table3_id
     WHERE product_table2.product_id IN ( SELECT id FROM products_table WHERE active = 'yes' )
       AND table3.active = 'yes'
     GROUP BY product_idc) AS product_sales
 GROUP BY product_id

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270623

Just do an aggregation:

select product_id, sum(quantity) as quantity, sum(amount) as amount
from (<your query here>)
group by product_id

I would add that you probably want to use union all instead of union. In the event that the two sources return exactly the same values, you probably want to keep both (union removes duplicates).

Upvotes: 3

Related Questions