Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
mosiki
Starting Member
12 Posts |
Posted - 2013-06-18 : 05:03:38
|
I'm attempting to create three Summary columns using the Count function and Group By statement.The below code gives this result.ManagerTeam ManagerName Season Competition LostArsenal Arsene Wenger 2012-13 Premier League 3SELECT Soccer_Base.dbo.Managers.ManagerTeam ,Soccer_Base.dbo.Managers.ManagerName ,Soccer_Base.dbo.Results.Season ,Soccer_Base.dbo.Results.Competition ,Count('Loss') as Lost FROM Soccer_Base.dbo.Managers JOIN Soccer_Base.dbo.Results ON Soccer_Base.dbo.Managers.ManagerTeam = Soccer_Base.dbo.Results.HomeTeam WHERE Soccer_Base.dbo.Results.HomeFT< Soccer_Base.dbo.Results.AwayFT GROUP BY ManagerTeam , ManagerName, Season, Competition HAVING ManagerTeam = 'Arsenal' I want to add two more columns to count draws and losses, whereSoccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFTANDSoccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFTI'm unsure as to use sub-queries or union or if either would work. Anyone have any ideas?Many ThanksMosiki. |
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-06-18 : 05:08:31
|
You can add columns as COUNT(CASE WHEN Conditions THEN 1 END) AS DrawsCount--Chandu |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-18 : 05:08:45
|
[code]SELECT Soccer_Base.dbo.Managers.ManagerTeam ,Soccer_Base.dbo.Managers.ManagerName ,Soccer_Base.dbo.Results.Season ,Soccer_Base.dbo.Results.Competition ,Count('Loss') as Lost ,Sum( case when Soccer_Base.dbo.Results.HomeFT > Soccer_Base.dbo.Results.AwayFT then 1 else 0 end ) as [Wins], ,Sum( case when Soccer_Base.dbo.Results.HomeFT = Soccer_Base.dbo.Results.AwayFT then 1 else 0 end ) as [Draws], ,Sum( case when Soccer_Base.dbo.Results.HomeFT < Soccer_Base.dbo.Results.AwayFT then 1 else 0 end) as [Losses],FROM Soccer_Base.dbo.Managers JOIN Soccer_Base.dbo.Results ON Soccer_Base.dbo.Managers.ManagerTeam = Soccer_Base.dbo.Results.HomeTeamWHERE Soccer_Base.dbo.Results.HomeFT < Soccer_Base.dbo.Results.AwayFTGROUP BY ManagerTeam ,ManagerName ,Season ,CompetitionHAVING ManagerTeam = 'Arsenal'[/code] KH[spoiler]Time is always against us[/spoiler] |
|
|
mosiki
Starting Member
12 Posts |
Posted - 2013-06-18 : 05:17:47
|
Perfect, cheers guys. |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2013-06-18 : 05:19:36
|
you might also wants to change this to WHERE clause. It is not really wrong, it is just it is unconventionalHAVING ManagerTeam = 'Arsenal' KH[spoiler]Time is always against us[/spoiler] |
|
|
|
|
|
|
|