mysql - How can i fix my (complicated) query so that it gives me proper table? -

so have query:

select a.year, a.avg_kw_price, b.avg_kw_consumed (select year, avg(value) avg_kw_price   ap_data substring(series_id, 8) ='72610'   group year)a,  (select substring(yyyymm,1,4) year, value*9506632 avg_kw_consumed  residential_en_consumption msn = 'esrcbus' ,   substring(yyyymm, 4,2) = '13'  group substring(yyyymm,1,4)) b a.year = b.year; 

my 2 tables this: ap_data: (the 72610 part refers item kw/h)

series_id           year    period      value apu000072610        1996    m11        0.092               apu000072610        1996    m12        0.092               apu000072610        1997    m01        0.092               apu000072610        1997    m02        0.092               apu000072610        1997    m03        0.093               apu000072610        1997    m04        0.092 

table residential_en_consumption looks (the 13 @ end of yyyymm item refers total year):

msn      yyyymm     value    description    unit esrcbus  201008      200     electricity    trillion btu esrcbus  201112      396     electricity    trillion btu esrcbus  201113      1200    electricity    trillion btu esrcbus  201213      2000    electricity    trillion btu 

what want table this:

year     avg_kw_price      avg_kw_consumed 2011     1.2                 158049 2012     0.9                 120310 

as of right i'm getting empty table query im using. also, can see multiply value 9506632 because did math , thats how go trillion btu per year kw/hour. not important , im not sure if right if must take out , keep in btu. how can fix query gives me table wanted, if want, disregard unit conversion?

a strategy use is: create temporary tables pieces of data need, create appropriate indexes on them, , put (in other words: divide-and-conquer strategy).

so, let's see can done:

-- subquery "a": drop table if exists temp_step01; create temporary table temp_step01     select year, avg(value) avg_kw_price      ap_data      substring(series_id, 8) ='72610'      group year; alter table temp_step01     add index idx_year(year); -- subquery "b": -- i'll split process in two, ease things bit drop table if exists temp_step02; create temporary table temp_step02     select cast(substring(yyyymm,1,4) unsigned int) year -- it's easier                                                                 -- handle numbers          , value*9506632 avg_kw_consumed      residential_en_consumption      msn = 'esrcbus'; alter table temp_step02     add index idx_year(year); drop table if exists temp_step03; create temporary table temp_step03     select year          , avg_kw_consumed -- think should aggregate value somehow     temp_step02     group year; -- finally: select a.year, avg_kw_price, avg_kw_consumed temp_step01      inner join temp_step03 b on a.year = b.year 

you should check calculations correct, think idea clear:

1. filter data first table , put in temporary table.

   add appropriate indexes table.

2. filter data second table , put in temp table.

   add appropriate indexes table.

3. do whatever calculations need do. if needed, use additional temp tables.

   insist: add appropriate indexes table.

4. pull data need in last, simple, query.

use temp tables perform "heavy-lifting" tasks: filtering, aggregating, etcetera. key perform steps separatedly, , put them @ end, using indexed fields of each 1 optimize appropriate joins.

remember: temporary tables visible connection creates them, , dropped once connection closed or killed.