Can this be done in one Query?

[kennedyr4]

I have successfullycreated the following Union Query in my PHP code but now wish to Distinct Select the tTeamName and SUM the other columns. Can this be done in one query (same query) or do I need to Create Table first then re-query? I have had no luck trying to create a temp table so if that is the route I must go, I could use further assistance.

Thanks in advance.

HERE IS MY CODE

$sql = 'SELECT tDiv, tHomeLeagueAS tLeague , tHomeTeamAS tTeamName , tHomeScoreAS tPtsFor , tAwayScoreAS tPtsAgainst , 1 AS tWin, 0 AS tLose, 0 AS tTie FROM tblGames WHERE (tHomeScore > tAwayScore)'
. ' UNION SELECT tDiv, tHomeLeagueAS tLeague , tHomeTeamAS tTeamName , tHomeScoreAS tPtsFor , tAwayScoreAS tPtsAgainst , 0 AS tWin, 1 AS tLose, 0 AS tTie FROM tblGames WHERE (tHomeScore < tAwayScore)'
. ' UNION SELECT tDiv, tAwayLeagueAS tLeague , tAwayTeamAS tTeamName , tAwayScoreAS tPtsFor , tHomeScoreAS tPtsAgainst , 0 AS tWin, 1 AS tLose, 0 AS tTie FROM tblGames WHERE (tHomeScore > tAwayScore)'
. ' UNION SELECT tDiv, tAwayLeagueAS tLeague , tAwayTeamAS tTeamName , tAwayScoreAS tPtsFor , tHomeScoreAS tPtsAgainst , 1 AS tWin, 0 AS tLose, 0 AS tTie FROM tblGames WHERE (tHomeScore < tAwayScore)'
. ' UNION SELECT tDiv, tAwayLeagueAS tLeague , tAwayTeamAS tTeamName , tAwayScoreAS tPtsFor , tHomeScoreAS tPtsAgainst , 0 AS tWin, 0 AS tLose, 1 AS tTie FROM tblGames WHERE (tHomeScore = tAwayScore)'
. ' UNION SELECT tDiv, tHomeLeagueAS tLeague , tHomeTeamAS tTeamName , tHomeScoreAS tPtsFor , tAwayScoreAS tPtsAgainst , 0 AS tWin, 0 AS tLose, 1 AS tTie FROM tblGames WHERE (tHomeScore = tAwayScore)';

           $result= mysql_query($sql) or die("Query Failed"); [/COLOR]

[tomhath]

I don't think you can get the total the way you have it written, because each SELECT will return some rows for each team; you need all the rows for the team in a single select in order to SUM them.

Can you build the totals in PHP code? The query to build the totals in SQL wouldn't be too difficult but it would be a lot different than this.