Loading

How to Group data In Sql Server Query

How to Group data In Sql Server Query

Mohit Kala

Mohit Kala

8y907

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

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nitin Sontakke8y ago
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!
Nilesh Shah8y ago
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.