title: multiple nested queries
body: <p>I am currently trying to optimize a report using mysql and openreports for accounting use.  Although I'm sure there are better tools for the job, these are what I have to work with at the moment.  I am doing the following calculated fields:</p>
  
  <p>client,
  number of orders,
  client cost, 
  client adjustments,
  adjusted client price (client cost - client adjustments),
  Vendor cost,
  vendor adjustments,
  adjusted vendor price (vendor cost - vendor adjustments),
  margin ((client billed - vendor billed)/client billed),
  adj margin ((adj client price-adj vendor price)/adj client)</p>
  
  <p>The tables are quite large and I have to join 4 tables for each inner join.  The report that was in place before has a union and about 20 nested selects in each side of the union all containing at least four joins and counts.  Since I'm doing calculations based on previous fields, each nested select gets progressively larger containing all of the nested selects, counts, and calculations from the prior calculations.  The query is now 400 lines and very expensive and taxing to the system especially on larger clients.  </p>
  
  <p>I realize there are better tools for the job but I was wondering what the ideal fix would be for the situation.  Could I create a user defined variable within the query and use it later?  Would this work if I had multiple queries per client?  I can't include the entire 400 line query in this post but I can supply any additional details that wold be helpful.  Any insight would be greatly appreciated.  </p>
  
  <pre><code>select office_1.name as 'Client'
  ,count(distinct(property_1.id)) as 'Total Billed Orders',
  (select format(coalesce(sum(serviceprice_2.amount),0),2)
      from cap.service_price serviceprice_2
      inner join cap.service service_2 on service_2.id = serviceprice_2.service_id
      inner join cap.service_area servicearea_2 on servicearea_2.id = service_2.service_area_id
      inner join cap.property property_2 on property_2.id = servicearea_2.property_id
      inner join cap.office office_2 on office_2.id = property_2.client_id
      where serviceprice_2.price_context = 'CLIENT'
      and serviceprice_2.price_type_id = 236
      &amp;&amp; service_2.date_client_billed between '2012-09-01' and now()
      and service_2.gl_code_ap='4700-70-000'
      and service_2.date_cancelled is null
      and office_2.id = office_1.id) as 'Client Price'
  /* other calculations between here */
  from cap.service service_1
  inner join cap.service_area servicearea_1 on servicearea_1.id = service_1.service_area_id
  inner join cap.property property_1 on property_1.id = servicearea_1.property_id
  inner join cap.office office_1 on office_1.id = property_1.client_id
  inner join cap.office vendor_1 on vendor_1.id = service_1.vendor_id
  where 
  service_1.date_client_billed between '2012-09-01' and now()
  and service_1.date_cancelled is null
  and office_1.id = 26377 
  and service_1.gl_code_ap='4700-70-000'
  group by office_1.id
  ;
  </code></pre>