two queries - SQL Server Forums

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

two queries

Author

Topic

robcpettit
Starting Member

11 Posts

Posted - 2013-08-31 : 12:23:24

Hi, I have a football match results table with HomeTeam, AwayTeam, FTHG
(home goals), FTAG(away goals) and a few other columns. Im usng this code to find first the home wins, losses and draws, then the away wins, losses and draws. Can I join these as one view. At the momment i get two views/tables. I just need pointing in the right direction.
USE [FootballPredictor]
GO

SELECT cast(HomeTeam as nvarchar(100)) HomeTeam,
--home wins, losses and draws
Count(FTHG)+ Count(FTAG) as P,

--gives me home data
SUM(case When (FTHG > FTAG) THEN 1 ELSE 0 END)
AS HW,
SUM(case When (FTHG < FTAG) THEN 1 ELSE 0 END)
AS HL,

SUM(case When (FTHG = FTAG) THEN 1 ELSE 0 END)
AS HD

FROM [dbo].[testTable]

GROUP BY cast(HomeTeam as nvarchar(100))

SELECT cast(AwayTeam as nvarchar(100)) AwayTeam,

Count(FTHG)+ Count(FTAG) as P,
--gaway wins, losses and draws

SUM(case When (FTHG > FTAG) THEN 1 ELSE 0 END)
AS AW,
SUM(case When (FTHG < FTAG) THEN 1 ELSE 0 END)
AS AL,

SUM(case When (FTHG = FTAG) THEN 1 ELSE 0 END)
AS AD

FROM [dbo].[testTable]

GROUP BY cast(AwayTeam as nvarchar(100))

GO
any help appreciated
Robert

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-31 : 16:29:35

Maybe something like:


select team
      ,sum(p) as p
      ,sum(hw) as hw
      ,sum(hl) as hl
      ,sum(hd) as hd
      ,sum(aw) as aw
      ,sum(al) as al
      ,sum(ad) as ad
  from (select hometeam as team
              ,count(fthg) as p
              ,sum(case when fthg>ftag then 1 else 0 end) as hw
              ,sum(case when fthg<ftag then 1 else 0 end) as hl
              ,sum(case when fthg=ftag then 1 else 0 end) as hd
              ,0 as aw
              ,0 as al
              ,0 as ad
          from footballpredictor.dbo.testtable
         group by hometeam
        union all
        select awayteam as team
              ,count(ftag) as p
              ,0 as hw
              ,0 as hl
              ,0 as hd
              ,sum(case when fthg<ftag then 1 else 0 end) as aw
              ,sum(case when fthg>ftag then 1 else 0 end) as al
              ,sum(case when fthg=ftag then 1 else 0 end) as ad
          from footballpredictor.dbo.testtable
         group by awayteam
       ) as a
 group by team
 order by team

robcpettit
Starting Member

11 Posts

Posted - 2013-09-01 : 05:51:35

Thank you, this works brilliantly. I shall now read up on why and how.
Thank you again regards Robert

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources