CODEWITHSUNDEEP
mysql
Aamir Rind
Aamir Rind

Reputation: 39659

Complex MySQL query using group by

Lets say i have two tables

Table1:
product_id, design1, design2
1              A        C
2              B        A

Table2:
product_id, value
1             10
2             10

Now i want to to sum all the value for particular design for all products.

SELECT designA, SUM(value) FROM ( 
 SELECT b.design1 AS designA, SUM(value) AS value FROM table2 AS a LEFT JOIN table1 AS b ON a.product_id = b.product_id GROUP BY b.design1) AS T GROUP BY designA

It gives me this:
designA SUM(value)
  A           10
  B           10

Now the problem is that if user has specified design2 in table1 then what ever is the value of design1 will automatically be added in design2. If design2 is not present design1 column then it will be a new row of result:

Desited result is this:

designA SUM(value)
  A            20
  B            10
  C            10

Upvotes: 0

Views: 78

Answers (2)

Stevo
Stevo

Reputation: 2639

select y.designA, sum(value) from 

(select  a.design1 as designA, value from
Table1 as a
inner join Table2 as b
on
a.product_id = b.product_id

union all

select a.design2 as designA, value from
Table1 as a
inner join Table2 as b
on
a.product_id = b.product_id) as y 
group by y.designA

seems to work for your test data, not tried on other configurations but you should be able to tweak it, if you understand what it's doing.

Upvotes: 1

Scott Hunter
Scott Hunter

Reputation: 49803

UNION in the matches based on design2:

SELECT designA, SUM(value) FROM ( 
 SELECT b.design1 AS designA, SUM(value) AS value FROM table2 AS a LEFT JOIN table1 AS b ON a.product_id = b.product_id GROUP BY b.design1
UNION
 SELECT b.design2 AS designA, SUM(value) AS value FROM table2 AS a LEFT JOIN table1 AS b ON a.product_id = b.product_id GROUP BY b.design2
) AS T GROUP BY designA

Upvotes: 0

Related Questions