SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Grouping or Union?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mosiki
Starting Member

12 Posts

Posted - 06/18/2013 :  05:03:38  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2206 Posts

Posted - 06/18/2013 :  05:08:31  Show Profile  Reply with Quote
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)

Singapore
17608 Posts

Posted - 06/18/2013 :  05:08:45  Show Profile  Reply with Quote
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'



KH
Time is always against us

Go to Top of Page

mosiki
Starting Member

12 Posts

Posted - 06/18/2013 :  05:17:47  Show Profile  Reply with Quote
Perfect, cheers guys.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17608 Posts

Posted - 06/18/2013 :  05:19:36  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000