How to SUM of SUMS of fields with same value in MYSQL -

i have these table: need "sum" values same key1 & key2 when key3 = x

| key1 | value1 | key2 | value2 | key3 | |   1  |   9    |   2  |   2    |   x  | |   1  |   10   |   2  |   3    |   x  | |   2  |   5    |   1  |   8    |   x  | |   2  |   5    |   1  |   9    |   y  | 

select key1 k1, key2 k2 table1 key3 = x

for each result of these query need do:

select  coalesce(sum(case when key1 = k1 totalamount1 else 0   end),0) +  coalesce(sum(case when key2 = k2 totalamount2 else 0   end),0) total table1 key3 = x 

y need these output:

      total id 1:  27 id 2:  10  

how can join thes 2 query?

thanks!

here's 1 way result:

select if(i.i,t.key1,t.key2) `id`      , sum(if(i.i,t.value1,t.value2)) `total`   table1 t  cross   join (select 0 union select 1)  t.key3 = 'x'  group     if(i.i,t.key1,t.key2) 

sql fiddle demo here: http://sqlfiddle.com/#!2/d7599f/1

---

the "trick" used 2 sets of rows, performing cross join of table1 set containing 2 rows; 2 rows can distinguished each other conditional test.

since value of i.i either 1 or 0, means evaluate true , false conditional test within if function.

we can extract (key1, value1) 1 set of rows, , extract (key2,value2) other set, essentially, creating result set this:

| | keyn | valuen |  | 1 |   1  |   9    |  | 1 |   1  |   10   |  | 1 |   2  |   5    |  | 0 |   2  |   2    |  | 0 |   2  |   3    |  | 0 |   1  |   8    | 

now, it's matter of doing sum(valuen) group keyn

---

the query above rewritten in (more portable) sql-92 style:

select case when i.i = 1 t.key1 else t.key2 end `id`      , sum(case when i.i = 1 t.value1 else t.value2) `total`   table1 t  cross   join (select 0 union select 1)  t.key3 = 'x'  group     case when i.i = 1 t.key1 else t.key2 end `id` 

(for oracle, we'd need add from dual on each select in inline view aliased i.)

---