davidgagne.net

  • About
  • Archives
  • Twitter
  • Facebook
  • bartender.live
  • GitHub
  • LinkedIn

SQL

October 16th, 2001 @ 1:53 am PDT

Help! If anyone can help me out with this I will severely owe you one. I have this SQL query that I need to return just one more field, and I can’t figure out how to do it. (The query is in the extended text for this entry; click on the icon or the comments link to get there!) Currently the query needs the parameter [TeamID] to run, and it returns the Sum of the Wins and Losses for the [TeamID]. (The Sum of the Wins is the Sum of the AwayWins and the Sum of the HomeWins. And the Sum of the Losses is the same except for Losses instead of Wins …) What I want to be able to do is to have it return the [TeamID] as well, so that I can build another query on top of this one. Anyone? Anyone? Please eMail me if you have any thoughts …

SELECT (SELECT [AwayLosses].[CountOfGameID]+[HomeLosses].[CountOfGameID] AS Losses FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND [tblSeason].[AwayScore] < [tblSeason].[HomeScore]) AS AwayLosses, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] AND tblSeason.HomeScore < tblSeason.AwayScore) AS HomeLosses;) AS TotalLosses, (SELECT HomeWins.CountOfGameID+AwayWins.CountOfGameID AS Wins FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] And [HomeScore] > tblSeason.[AwayScore]) AS HomeWins, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND tblSeason.AwayScore > tblSeason.HomeScore) AS AwayWins) AS TotalWins
FROM (SELECT AwayLosses.CountOfGameID, HomeLosses.CountOfGameID FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] AND tblSeason.HomeScore < tblseason .AwayScore) AS HomeLosses, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND [tblSeason].[AwayScore] < [tblSeason].[HomeScore]) AS AwayLosses) AS Losses, (SELECT AwayWins.CountOfGameID, HomeWins.CountOfGameID FROM (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.AwayID=[TeamID] AND tblSeason.AwayScore > tblSeason.HomeScore) AS AwayWins, (SELECT Count(tblSeason.GameID) AS CountOfGameID FROM tblSeason WHERE tblSeason.HomeID=[TeamID] And [HomeScore] > tblSeason.[AwayScore]) AS HomeWins) AS Wins

More posts tagged:
code / football / Programming / sql

More posts categorized:
media Programming

More posts from:
October 2001 / 2001

Post navigation

Previous Post
Previous Post SotM
Next Post
Next Post SQL

Responses to “SQL”

  1. October 16th, 2001 @ 2:01 am
    dvg

    Actually now that I think about it: If anyone has any ideas on a better database structure, I’m open to that too. Right now there’s tblSeason which has [GameID] as a primary key, [AwayID], [HomeID] as the links to tblTeams, [HomeScore] and [AwayScore] storing the scores for each game, and a [WeekID] which is a link to tblWeeks.

  2. October 18th, 2001 @ 1:25 pm
    hamhock

    Can you send me the database? Can’t promise you anything but I may be able to talk a couple of friends of mine into looking at it. What’s the finders fee for a solution or are you heavily into that “open source” thing? 🙂

Comments Closed

New comments are disabled on this post.

Search


Give Easily

Give Easily

Did you know that Amazon will donate to the charity of your choice when you use smile.amazon.com to do your online shopping? It doesn't cost you anything extra, and you can make a big difference just by using this link.

  • Support A Charity

© David Vincent Gagne. All rights reserved. Custom WordPress Theme by Jacket Industries.