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.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Grouping or Union?

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 Lost
Arsenal Arsene Wenger 2012-13 Premier League 3


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

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

Soccer_Base.dbo.Results.HomeFT= Soccer_Base.dbo.Results.AwayFT

AND

Soccer_Base.dbo.Results.HomeFT> Soccer_Base.dbo.Results.AwayFT


I'm unsure as to use sub-queries or union or if either would work. Anyone have any ideas?

Many Thanks

Mosiki.

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
Go to Top of Page

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.HomeTeam
WHERE Soccer_Base.dbo.Results.HomeFT < Soccer_Base.dbo.Results.AwayFT
GROUP BY
ManagerTeam
,ManagerName
,Season
,Competition
HAVING ManagerTeam = 'Arsenal'
[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page

mosiki
Starting Member

12 Posts

Posted - 2013-06-18 : 05:17:47
Perfect, cheers guys.
Go to Top of Page

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 unconventional

HAVING ManagerTeam = 'Arsenal'



KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -