Sports Results Query... - 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

Sports Results Query...

Author

Topic

1_PAblo_Angel
Starting Member

9 Posts

Posted - 2012-02-28 : 16:35:39

Hi,

I've made a simple database containing football results, with goals scored and conceded, etc. I'm trying to make a query which will display a certain number of results in date order for a selected team; I've got it working for the last x home or away games but am not sure how to go about putting these queries together so to speak... perhaps with a Case statement? Or sub selects?

Not after anyone doing it for me (unless they want to ;)) but a nudge in the right direction would be great. The queries I've got so far are below:


SELECT Hometeam As Opponents, 
       FTAG AS Scored, FTHG AS Conceded,  FTAG - FTHG AS Difference
        FROM (SELECT TOP 5 * FROM Gamblor.dbo.prem Where awayteam = 'Aston Villa' order by Date desc) as away


SELECT Awayteam As Opponents, 
FTHG AS Scored, FTAG AS Conceded, FTHG - FTAG AS Difference 
FROM (SELECT TOP 5 * FROM Gamblor.dbo.Prem Where hometeam = 'Aston Villa' order by Date desc) as home

USE [Gamblor]
GO
/****** Object:  Table [dbo].[Prem]    Script Date: 02/29/2012 21:00:26 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Prem](
	[Div] [nvarchar](255) NULL,
	[Date] [date] NULL,
	[HomeTeam] [nvarchar](255) NULL,
	[AwayTeam] [nvarchar](255) NULL,
	[FTHG] [float] NULL,
	[FTAG] [float] NULL,
	[FTR] [nvarchar](255) NULL,
	[HTHG] [float] NULL,
	[HTAG] [float] NULL,
	[HTR] [nvarchar](255) NULL,
	[Referee] [nvarchar](255) NULL,
	[HS] [float] NULL,
	[AS] [float] NULL,
	[HST] [float] NULL,
	[AST] [float] NULL,
	[HF] [float] NULL,
	[AF] [float] NULL,
	[HC] [float] NULL,
	[AC] [float] NULL,
	[HY] [float] NULL,
	[AY] [float] NULL,
	
 [float] NULL,
	[AR] [float] NULL
        ) ON [PRIMARY]
GO
/****** Object:  View [dbo].[Premteams]    Script Date: 02/29/2012 21:00:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[Premteams]
AS
SELECT DISTINCT HomeTeam AS team
FROM         dbo.Prem
GO

insert into gamblor.dbo.Prem values('E0', 2012-01-14,	'Aston Villa',	'Everton',	1,	1,	'D', 0,	0,	'D',	'M Clattenburg',	17,	7,	11,	4,	12,	11,	9,	3,	1,	1,	0,	0)

Please let me know if I've described anything in the wrong way, or missed anything useful out. What I'd like to be able to do is retrieve , say, the last 10 games where the chosen team is either the home or away side. And switch the conceded/scored values respectively so they're still correct.

-Thanks in advance-

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-28 : 16:46:41

Do you have a table that describes falling down like you were shot by a high powered rifle, then getting up like there wasn't a problem?

..oh..back to your problem..you problem need these tables

PERSON
TEAM
SCHEDULE
OUTCOME
CARDS (PENALTIES)
SCORING
REFEREES
REFEREES SHOT
FANS
STADIUM
FANS BURNING DOWN STADIUMS
COUNTRY
WARS

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

1_PAblo_Angel
Starting Member

9 Posts

Posted - 2012-02-29 : 03:44:10

Thanks for that Brett.

If I neglected any aspects of the question, or framed it wrong, a smart arsed post like that is bound to constructively help me to correct any errors.

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 10:23:06

Well, the first set of tables would be the way to go

What's the Gamblor.dbo.prem table look like

Seems like you've got a bunch of data in this table that is denormalized

I'm suggesting that the data be normalized...kinda like the first set of tables I mentioned

Do you know how to post DDL? And can you post sample Data (in DML Form), so we can see what it looks like?

And, hey, I though it was funny

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

1_PAblo_Angel
Starting Member

9 Posts

Posted - 2012-02-29 : 12:11:17

No problem.

Having seen your post, I figured I'd missed out some relevant info so I had a look through the guide in your sig. Soon as I get home from work I'll update OP with DDL, DML etc...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 13:54:09

quote:
Originally posted by 1_PAblo_Angel

No problem.

Having seen your post, I figured I'd missed out some relevant info so I had a look through the guide in your sig. Soon as I get home from work I'll update OP with DDL, DML etc...

that would be really helpful for someone who is trying to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

1_PAblo_Angel
Starting Member

9 Posts

Posted - 2012-02-29 : 16:16:42

quote:
Originally posted by visakh16

quote:
Originally posted by 1_PAblo_Angel

No problem.

Having seen your post, I figured I'd missed out some relevant info so I had a look through the guide in your sig. Soon as I get home from work I'll update OP with DDL, DML etc...

that would be really helpful for someone who is trying to help you out

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I'm sorry if I came across as rude there, I think I misinterpreted X002548's initial reply.

OP updated now and yeah, very sorry if I came across as a dick earlier...

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-29 : 16:23:45

Nah, forget it...my middle name should be Obfuscate

I don't understand what your other columns are for, and yes it would make your life easier if the data was normalized

However, Can you post what the sample Results would look like? That might help

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

1_PAblo_Angel
Starting Member

9 Posts

Posted - 2012-03-01 : 15:41:56

The other columns aren't important, they're just included in the data source... for the purposes of this query, all that matters is
hometeam
awayteam
fthg (full time home goals)
ftag (full time away goals)

Sample line of data:

Div	Date	        HomeTeam	AwayTeam	FTHG	FTAG	FTR	HTHG	HTAG	HTR	
      E0	2012-01-14	Aston Villa	Everton	          1	1	D	0	0	D

Example of what each of my current queries returns:

Opponents	Scored	Conceded	Difference
       Man City	0	1	         -1
        QPR	2	2	          0
      Everton	1	1	          0
      Swansea	0	2	         -2
      Arsenal	1	2	         -1

The example above is for the last 5 games where the away team was 'Aston Villa'. I'm trying to make it so that I create a table like that, for the last 5 games where the awayteam was Aston Villa OR the hometeam was Aston Villa... but displayed in the same columns, so calculating difference between FTHG and FTAG would be the other way around.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 12:28:05

what are FTHG,FTAG etc? how do you calculate counts from it? can you explai?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

1_PAblo_Angel
Starting Member

9 Posts

Posted - 2012-03-07 : 07:07:15

FTHG = full time home goals = Scored or conceded
FTAG = Full time away goals = Scored or conceded

Subtract one from the other to get the difference, but if the selected team is the awayteam this has to be done in reverse.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-07 : 19:04:10

sorry you're not doing much favour for yourself here

see below on how you need to give detail for us to understand the issue

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources