title: Complex multi table mysql query
body: <p>Im Looking for some help with a mysql query to return the below data from a few different tables for my international call billing system</p>
  
  <p>In the database i have a table that contains each of the call records, billing rates &amp; the final group which i want to display on the invoice.</p>
  
  <p>What im looking to do is iterate through the table tbl_international_rates and for each row sum the CDR_ChargedDurationMinutes in tbl_international_records WHERE CDR_BillerCode=international_rate_prefix then * international_rate_price returing a total amount of charged minutes and a prefix total or something that looks like the below.</p>
  
  <pre><code>|--------------------------|--------------------------|--------------------------|
  |        Prefix            |      total minutes       | total amount         |
  |--------------------------|--------------------------|--------------------------|
  |         93               |          14.3            |         $137.28          |
  |--------------------------|--------------------------|--------------------------|
  |         937              |          31.2            |         $418.08          |
  |--------------------------|--------------------------|--------------------------|
  </code></pre>
  
  <p>once that is complete i would like to group them together by there parent group returning something from mysql that look like the below table.</p>
  
  <pre><code>|--------------------------|--------------------------|--------------------------|
  |  CountryDescription      |   CountryTotalMinutes    |      CountryTotalCosts   |
  |--------------------------|--------------------------|--------------------------|
  |        Afghanistan       |         45.5             |           555.36         |
  |--------------------------|--------------------------|--------------------------|
  </code></pre>
  
  <p>is this possible?</p>
  
  <p>This is the structure of the table in the database</p>
  
  <p>tbl_international_groups</p>
  
  <pre><code>|--------------------------|--------------------------------|
  | international_group_idno | international_group_description|
  |--------------------------|--------------------------------|
  |            1             |         Afghanistan            |
  |--------------------------|--------------------------------|
  |            2             |         USA                    |
  |--------------------------|--------------------------------|
  </code></pre>
  
  <p>tbl_international_rates</p>
  
  <pre><code>|--------------------------|--------------------------|--------------------------|--------------------------|
  | international_rate_idno  | international_rate_prefix| international_rate_group | international_rate_price |
  |--------------------------|--------------------------|--------------------------|--------------------------|
  |            1             |         93               |          1               |          9.60            |
  |--------------------------|--------------------------|--------------------------|--------------------------|
  |            1             |         937              |          1               |          13.40           |
  |--------------------------|--------------------------|--------------------------|--------------------------|
  </code></pre>
  
  <p>tbl_international_records</p>
  
  <pre><code>|--------------------------|--------------------------|--------------------------|
  |          CDR_ID          |CDR_ChargedDurationMinutes|      CDR_BillerCode      |
  |--------------------------|--------------------------|--------------------------|
  |            1             |         4.7              |           93             |
  |--------------------------|--------------------------|--------------------------|
  |            2             |         5.6              |           97             |
  |--------------------------|--------------------------|--------------------------|
  |            3             |         9.6              |           93             |
  |--------------------------|--------------------------|--------------------------|
  |            4             |         25.6             |           97             |
  |--------------------------|--------------------------|--------------------------|
  </code></pre>
  
  <p>****EDIT******</p>
  
  <p>Using the below query i am able to return the table like the below however how can i group them by there parent in tbl_international_groups</p>
  
  <pre><code>|--------------------------|--------------------------|--------------------------|
  |        Prefix            |      total minutes       | total amount         |
  |--------------------------|--------------------------|--------------------------|
  |         93               |          14.3            |         $137.28          |
  |--------------------------|--------------------------|--------------------------|
  |         937              |          31.2            |         $418.08          |
  |--------------------------|--------------------------|--------------------------|
  </code></pre>
  
  <p>The Query</p>
  
  <pre><code>select rates.international_rate_prefix as Prefix
   , sum( records.CDR_ChargedDurationMinutes ) as total_minutes
   , sum( records.CDR_ChargedDurationMinutes ) * 
  rates.international_rate_retail as total_amount
  from tbl_international_rates as rates
  join tbl_international_records as records
    on rates.international_rate_prefix = records.CDR_BillerCode
  group by rates.international_rate_prefix
  </code></pre>