How to Group data In Sql Server Query

Question

select cf.Name as FromName, tf.NoOfGoalFrom,ct.Name as ToName,tt.NoOfGoalTo from TournamentCurrentStatus t inner JOIN TournamentCurrentStatusFrom tf on t.TournamentCurrentStatusFromID=tf.TournamentCurrentStatusFromID inner JOIN TournamentCurrentStatusTo tt on t.TournamentCurrentStatusToID=tt.TournamentCurrentStatusToID inner JOIN Club as cf on tf.ClubIDFrom=cf.ClubID inner JOIN Club as ct on tt.ClubIDTo=ct.ClubID How to group like Kdma-10 Both Goals Grouping with team name

Nilesh Shah · Answer

Hi First advice is to change the table structure to a better relational model having only 3 columns i.e. Name, NoOfGoals and Direction (which indicates either To or From or 1 or 0 or something else) But considering the fact that you may not have time/liberty to redesign your table, I suggest to try this. It has two group by clauses with union all and again a final group by. Select a. Name , a. Sum (Goals) from ( select FromName as 'Name' , Sum (NoOfGoalFrom) as 'Goals' From YourTable Group By FromName Union All select ToName as 'Name' , Sum (NoOfGoalTo) as 'Goals' From YourTable Group By ToName ) a group by a. Name Let me know what happens.

Nitin Sontakke · Answer

Instead of having columns side-by-side, have them one below another and sum them, that's it. As shown below: select [ Name ], sum ([Goals]) [NoOfGoals] from ( select cf. Name , tf.NoOfGoalFrom [Goals] from TournamentCurrentStatus t inner JOIN TournamentCurrentStatusFrom tf on t.TournamentCurrentStatusFromID = tf.TournamentCurrentStatusFromID inner JOIN Club as cf on tf.ClubIDFrom=cf.ClubID union all select ct. Name , tt.NoOfGoalTo [Goals] from TournamentCurrentStatus t inner JOIN TournamentCurrentStatusTo tt on t.TournamentCurrentStatusToID = tt.TournamentCurrentStatusToID inner JOIN Club as ct on tt.ClubIDTo=ct.ClubID ) t group by [ Name ] order by [ Name ] Hope it helps!