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

I am beating my brain against this one

I have 3 SQL Server 2005 tables

userawards:

id, awardamount, userid, dateawarded, awardtypeid 

user:

id, firstname, lastname

awardtypes:

id, title

So if the awards table had the rows

1, 300.00, 3, 01-01-2011, 1
2, 125.00, 3, 01-05-2011, 1
3,  50.00, 2, 01-05-2011, 2

user table rows

1, john, smith
2, mark, smith
3, bob, smith

award types

1, cash
2, prize

and I want the output to look similar to this

bob smith, 425.00, cash
mark smith, 50, prize

etc etc.

A user can have multi开发者_如何学编程ple 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.

So my query is looking like this (i know it doesn't work)

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

Is this even possible?

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 ab
INNER JOIN awards AS a on ab.userid = a.userid
LEFT JOIN userinfo AS ui  
  ON ui.userid = a.userid
LEFT 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 AwardAmount
FROM Awards a
  INNER JOIN UserInfo ui ON ui.UserId = a.UserId
  INNER JOIN AwardTypes at ON at.AwardTypeId = a.AwardTypeId
GROUP BY ui.FirstName, ui.LastName, at.Title
ORDER BY ui.LastName, ui.FirstName

If I understand this correctly, there should be no more than one row per user and you need a total award amount per user. On other side you want to know what kind of awards every user had. You can show comma delimited list of awards for every user:

select 
    usr.FirstName,
    usr.LastName, 
    awd.AwardAmount,
    AwardTypes = (   
        select Title + ','
        from UserAwards uaw
            join AwardTypes awt on
                uaw.AwardTypeId = awt.Id
        where uaw.UserId = usr.id
        for xml path(''))
from [User] usr
    join (
        select UserId, sum(AwardAmount) AwardAmount
        from UserAwards uaw
        group by UserId
    ) awd on
        awd.UserId = usr.Id