title: SQL query to compare budget to actual expenses
body: <p>I'm struggling to build a query comparing budget to actual expense items.<br>
  The budget table has a single record per month/category on the first day of the month whereas the expense table has many records throughout the days of the month.</p>
  <p>My desired result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>YEAR</th>
  <th>MONTH</th>
  <th>category</th>
  <th>budgetAmt</th>
  <th>sumExpenseAmt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021</td>
  <td>1</td>
  <td>daily</td>
  <td>100</td>
  <td>49</td>
  </tr>
  <tr>
  <td>2021</td>
  <td>1</td>
  <td>monthly</td>
  <td>42</td>
  <td>87</td>
  </tr>
  <tr>
  <td>2021</td>
  <td>2</td>
  <td>daily</td>
  <td>101</td>
  <td>36</td>
  </tr>
  <tr>
  <td>2021</td>
  <td>2</td>
  <td>monthly</td>
  <td>55</td>
  <td>82</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I'm getting:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>YEAR</th>
  <th>MONTH</th>
  <th>category</th>
  <th>budgetAmt</th>
  <th>sumExpenseAmt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021</td>
  <td>1</td>
  <td>daily</td>
  <td>100</td>
  <td>85</td>
  </tr>
  <tr>
  <td>2021</td>
  <td>1</td>
  <td>monthly</td>
  <td>42</td>
  <td>169</td>
  </tr>
  <tr>
  <td>2021</td>
  <td>2</td>
  <td>daily</td>
  <td>101</td>
  <td>85</td>
  </tr>
  <tr>
  <td>2021</td>
  <td>2</td>
  <td>monthly</td>
  <td>55</td>
  <td>169</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The amounts in &quot;sumExpenseAmt&quot; are wrong AND they're repeating. <br>
  (85 is the sum of all expense-daily items (jan + feb): 40 + 9 + 32 + 4)<br>
  (169 is the sum of all expense-monthly items (jan + feb): 83 + 4 + 75 +7)</p>
  <p>MY SQL:</p>
  <pre><code>SELECT YEAR( &quot;b&quot;.&quot;date&quot; ) AS &quot;Year&quot;
  , MONTH( &quot;b&quot;.&quot;date&quot; ) AS &quot;Month&quot;
  , &quot;b&quot;.&quot;category&quot;
  , &quot;b&quot;.&quot;budgetAmt&quot;
  , SUM( &quot;e&quot;.&quot;expenseAmt&quot; ) AS &quot;sumExpenseAmt&quot;
  FROM &quot;budget&quot; AS &quot;b&quot;
  JOIN &quot;expense&quot; AS &quot;e&quot; ON &quot;b&quot;.&quot;category&quot; = &quot;e&quot;.&quot;category&quot;
  GROUP BY YEAR( &quot;b&quot;.&quot;date&quot; ), MONTH( &quot;b&quot;.&quot;date&quot; ), &quot;b&quot;.&quot;category&quot;, &quot;b&quot;.&quot;budgetAmt&quot;
  </code></pre>
  <p>table: budget</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>category</th>
  <th>budgetAmt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-01-01</td>
  <td>daily</td>
  <td>100</td>
  </tr>
  <tr>
  <td>2021-01-01</td>
  <td>monthly</td>
  <td>42</td>
  </tr>
  <tr>
  <td>2021-02-01</td>
  <td>daily</td>
  <td>101</td>
  </tr>
  <tr>
  <td>2021-02-01</td>
  <td>monthly</td>
  <td>55</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>table: expense</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>category</th>
  <th>expenseAmt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-01-04</td>
  <td>daily</td>
  <td>40</td>
  </tr>
  <tr>
  <td>2021-01-07</td>
  <td>daily</td>
  <td>9</td>
  </tr>
  <tr>
  <td>2021-01-08</td>
  <td>monthly</td>
  <td>83</td>
  </tr>
  <tr>
  <td>2021-01-25</td>
  <td>monthly</td>
  <td>4</td>
  </tr>
  <tr>
  <td>2021-02-01</td>
  <td>daily</td>
  <td>32</td>
  </tr>
  <tr>
  <td>2021-02-05</td>
  <td>daily</td>
  <td>4</td>
  </tr>
  <tr>
  <td>2021-02-15</td>
  <td>monthly</td>
  <td>75</td>
  </tr>
  <tr>
  <td>2021-02-20</td>
  <td>monthly</td>
  <td>7</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I've tried aggregating the expense table with a query and feeding the result into my initial SQL query, but that gives me the same result.</p>
  <p>query: qry_summary_expense</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>date</th>
  <th>category</th>
  <th>budgetAmt</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2021-01-01</td>
  <td>daily</td>
  <td>49</td>
  </tr>
  <tr>
  <td>2021-01-01</td>
  <td>monthly</td>
  <td>87</td>
  </tr>
  <tr>
  <td>2021-02-01</td>
  <td>daily</td>
  <td>36</td>
  </tr>
  <tr>
  <td>2021-02-01</td>
  <td>monthly</td>
  <td>82</td>
  </tr>
  </tbody>
  </table>
  </div>
  <pre><code>SELECT YEAR( &quot;b&quot;.&quot;date&quot; ) AS &quot;Year&quot;
  , MONTH( &quot;b&quot;.&quot;date&quot; ) AS &quot;Month&quot;
  , &quot;b&quot;.&quot;category&quot;, &quot;b&quot;.&quot;budgetAmt&quot;
  , SUM( &quot;e&quot;.&quot;expenseAmt&quot; ) AS &quot;sumExpenseAmt&quot; 
  FROM &quot;budget&quot; AS &quot;b&quot; 
  JOIN &quot;qry_summary_expense&quot; AS &quot;e&quot; ON &quot;b&quot;.&quot;category&quot; = &quot;e&quot;.&quot;category&quot; 
  GROUP BY YEAR( &quot;b&quot;.&quot;date&quot; ), MONTH( &quot;b&quot;.&quot;date&quot; ), &quot;b&quot;.&quot;category&quot;, &quot;b&quot;.&quot;budgetAmt&quot;
  </code></pre>