title: SQL Server Query LEFT JOIN, SUM and GROUP BY and I'm stumped!
body: <p>I am beating my brain against this one</p>
  
  <p>I have 3 SQL Server 2005 tables </p>
  
  <p><strong>userawards</strong>:</p>
  
  <pre><code>id, awardamount, userid, dateawarded, awardtypeid 
  </code></pre>
  
  <p><strong>user:</strong></p>
  
  <pre><code>id, firstname, lastname
  </code></pre>
  
  <p><strong>awardtypes:</strong> </p>
  
  <pre><code>id, title
  </code></pre>
  
  <p>So if the <code>awards</code> table had the rows</p>
  
  <pre><code>1, 300.00, 3, 01-01-2011, 1
  2, 125.00, 3, 01-05-2011, 1
  3,  50.00, 2, 01-05-2011, 2
  </code></pre>
  
  <p>user table rows</p>
  
  <pre><code>1, john, smith
  2, mark, smith
  3, bob, smith
  </code></pre>
  
  <p>award types</p>
  
  <pre><code>1, cash
  2, prize
  </code></pre>
  
  <p>and I want the output to look similar to this</p>
  
  <pre><code>bob smith, 425.00, cash
  mark smith, 50, prize
  </code></pre>
  
  <p>etc etc.</p>
  
  <p>A user can have multiple awards, the results need to display unique users, but with there total award amount. in addition there needs to be 2 joins, one, to grab the users first name/last that's in a user table and the award type title.</p>
  
  <p>So my query is looking like this (i know it doesn't work)</p>
  
  <pre><code>SELECT id, userid, awardtypeid, SUM(awardamount) 
  FROM awards a
  LEFT JOIN userinfo ui ON ui.userid = a,userid
  LEFT JOIN awardtypes ON awardtypesid = a.awardtypeid
  GROUP BY userid
  </code></pre>
  
  <p>Is this even possible?</p>