title: Multiple table joins with aggregate (mssql / sql server)
body: <p>Very simple question. What I want to do is select all columns from one table and sum of one column (which could have multiple matching rows) from another table.</p>
  
  <p>Example:</p>
  
  <pre><code>table ta (eid, uid, name, year, etc, etc, etc)
  table tb (eid, uid, name, year, amount, etc, etc)
  </code></pre>
  
  <p><code>eid</code> - will not match between both table
  <code>uid, name, year</code> - will match across both tables</p>
  
  <p>So I want to pull out all columns from table <code>ta</code>, simple:</p>
  
  <pre><code>select * from ta where eid='value';
  </code></pre>
  
  <p>I want to join amount column from table <code>tb</code> to my resultset, simple:</p>
  
  <pre><code>select a.*, b.amount
  from ta a
  inner join tb b on a.year=b.year
  where a.eid='value';
  </code></pre>
  
  <p>Great, this works fine. But what if I have multiple rows in table tb?</p>
  
  <p>Executing:</p>
  
  <pre><code>select a.*, sum(b.amount)
  from ta a inner join tb b on a.uid=b.uid
  where a.year='value';
  </code></pre>
  
  <p>gives me the following error:</p>
  
  <blockquote>
    <p>Column 'ta.eid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.</p>
  </blockquote>
  
  <p>So I add:</p>
  
  <pre><code>select a.*, sum(b.amount)
  from ta a inner join tb b on a.uid=b.uid
  where a.year='value' group by ta.uid;
  </code></pre>
  
  <p>And I get the same error!</p>
  
  <p>However, if I change my query to:</p>
  
  <pre><code>select a.uid, a.year, sum(b.amount)
  from ta a inner join tb b on a.uid=b.uid
  where a.year='value'
  group by ta.uid, ta.year;
  </code></pre>
  
  <p>It works, but now I have three columns instead of all columns that I wanted.</p>
  
  <p>So, at this point my question becomes: Is there a better, cleaner way of structuring this query other than me manually typing out all columns I want to pull from two tables with GROUP BY clause?</p>