SQL Server Query LEFT JOIN, SUM and GROUP BY and I'm stumped!

You probably want

SELECT 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,           awardtypeid 

or

SELECT userid,        SUM(awardamount) FROM   awards a        LEFT JOIN userinfo ui          ON ui.userid = a.userid        LEFT JOIN awardtypes          ON awardtypesid = a.awardtypeid GROUP  BY userid

This drops the id Column (probably not what you want to group on)

In the first case I included the awardtypeid in the select but this means you must also add that to the group by.

You can do it but the way you do it now is that it will show the number of awardamount, or bactually, the sum of awardamount - but per id, user, awardtypeid combination. You need to grab the user and sum of awards by itself, then join up with awardtype id - just be aware that the sum of award amounts is repeated for every awardtypeid

SELECT      ??.id ,     a.userid ,      a.awardtypeid ,      ab.awardamount ,     <whateverelse>FROM      (select userid, SUM(awardamount) as awardamount FROM awards GROUP BY userid) AS abINNER JOIN awards AS a on ab.userid = a.useridLEFT JOIN userinfo AS ui    ON ui.userid = a.useridLEFT JOIN awardtypes AS at   ON awardtypesid = a.awardtypeid 

By adding up the sum of awardamount per user before you join it in, you should be able to get what you want, without any grouping.

SELECT  ui.FirstName ,ui.LastName ,at.Title AS Award ,SUM(a.AwardAmount) AS AwardAmountFROM Awards a  INNER JOIN UserInfo ui ON ui.UserId = a.UserId  INNER JOIN AwardTypes at ON at.AwardTypeId = a.AwardTypeIdGROUP BY ui.FirstName, ui.LastName, at.TitleORDER BY ui.LastName, ui.FirstName