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.

I'd vote but I don't have the rep yet.. but yes this solves my problem. Thank you! – Sherdog May 3 at 17:35 1 @Sherdog thanks.So you know is a process for accepting answers which is similar to upvoting except only you can do it, and it doesn't require rep.

See this post. And please don't take this as me lobbying for my answer to be accepted. – Conrad Frix May 3 at 17:41 +1 for the ability to glean what the heck was desired from what was given (and for the answer).

– Thomas May 3 at 18:45 Thanks conrad, I have accepted your answer :) thanks for the heads up on that.. I looked right past the checkmark. And yes thanks for providing the answer with my bad attempt at explaining the situaiton :D – Sherdog May 3 at 20:09.

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 , 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.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.