title: SQL joining two tables with different levels of details
body: <p>So I have two tables of sales, budget and actual.</p>
  
  <p>"budget" has two columns: location and sales. For example,  </p>
  
  <p>location sales<br>
  24  $20000<br>
  36  $100300<br>
  40  $24700<br>
  Total $145000  </p>
  
  <p>"actual" has three columns: invoice_number, location, and sales. For example,  </p>
  
  <p>invoice location sales<br>
  10000 36 $5000<br>
  10001 40 $6000<br>
  10002 99 $7000<br>
  and so forth<br>
  Total   $110000  </p>
  
  <p>In summary, "actual" records transactions at the invoice level, whereas "budget" is done at the location level only (no individual invoices).</p>
  
  <p>I'm trying to create a summary table that lists actual and budget sales side by side, grouped by location. The total of the actual column should be $110000, and $145000 for budget. This is my attempt at it (on pgAdmin/ postgresql):</p>
  
  <pre><code>SELECT actual.location, SUM(actual.sales) AS actual_sales, SUM(budget.sales) AS budget_sales   
  FROM actual LEFT JOIN budget 
  ON actual.location = budget.location 
  GROUP BY actual.location;
  </code></pre>
  
  <p>I used LEFT JOIN because "actual" has locations that "budget" doesn't have (e.g. location 99).</p>
  
  <p>I ended up with some gigantic numbers ($millions) on both the actual_sales and budget_sales columns, far exceeding the total actual ($110000) or budget sales ($145,000). </p>
  
  <p>Is this because the way I wrote my query is basically asking SQL to join each invoice in "actual" to each line in "budget," therefore duplicating things many times over? If so how should I have written this? </p>
  
  <p>Thanks in advance!</p>