Help with union in sql statement

I am helping out a hockey league and am working with a sql database which has a table containing teamHome, homeWin, homeTie, homeWin, teamAway, awayWin, awayTie, awayLoss leagueId, divId,roundId. These represent the home and away games for the teams. There are several categories in the league for division and round.
My problem is that I am trying to get a statement to show the stats for the teams in Team, Win, Tie, Loss, Points. I am able to get the numbers that I am looking for but they remain separated in away games and home games and are stacked.

Here is the statement I have used:

SELECT teamHome AS team, SUM(homeWin) AS win, SUM(homeTie) as tie, SUM(homeLoss) as loss, SUM(homeWin)*2+SUM(homeTie)as points  

FROM `games`

WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'

group by team

UNION

SELECT teamAway AS team, SUM(awayWin) AS win, SUM(awayTie) as tie, SUM(awayLoss) as loss, SUM(awayWin)*2+SUM(awayTie) as points

FROM games
WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'

group by team


The result I get looks similar to this: (in this example the top 3 are the home games results and the bottom 3 are the away games results)

team     win     tie     loss  points
Team1    1         0       1        2
Team2     0         1      1        1
Team3     0         2       0        2
Team1     0         1       1        1
Team2      2         0      0        4
Team3     1          0       1       2

How can I get the final result to group
I am trying to get it to look like:

team     win     tie     loss  points
Team1    1        1        2       3
Team2    2        1        1        5
Team3    1         2       1       4

I just seem to be going in circles on this one. Any help would be appreciated. Thank you
K207Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

COANetworkCommented:
SELECT team, SUM(win) AS win, SUM(tie) as tie, SUM(loss) as loss, SUM(win)*2+SUM(tie)as points
FROM
(
SELECT teamHome AS team, homeWin AS win, homeTie as tie, homeLoss as loss

FROM games

WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'

group by team

UNION

SELECT teamAway AS team, awayWin AS win, awayTie as tie, awayLoss as loss

FROM games
WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'

group by team
) myTable

Open in new window

However, if you are working with large recordsets (even if you aren't, really) I would suggest populating a temporary table, or a table variable, with the result of your intermediary selects, and then doing the final select, the one with the aggregates, on that temp table.  The idea is to not use UNION at all - it is a very heavy, slow construct.  It is to be avoided whenever possible.

Try this for comparison:
SELECT teamHome AS team, homeWin AS win, homeTie as tie, homeLoss as loss
INTO #tmpMyTable
FROM games WITH(NOLOCK)
WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'
group by team

INSERT INTO #tmpMyTable (team, win, tie, loss)
SELECT teamAway AS team, awayWin AS win, awayTie as tie, awayLoss as loss
FROM games WITH(NOLOCK)
WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'
group by team

SELECT team, SUM(win) AS win, SUM(tie) as tie, SUM(loss) as loss, SUM(win)*2+SUM(tie)as points FROM #tmpMyTable

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K207Author Commented:
Thank you for your suggestion. I tried implementing the second statement and get a syntax error message
"#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM games WITH(NOLOCK) WHERE leagueId=1 AND divId=3 AND roundId=1 AND date=' at line 3"
Something about it that sql didn't like
0
K207Author Commented:
Hi COANetwork. I just tried the first suggestion. Initially it didn't come out with the results I had intended, but with a bit of manipulation - in the UNION parts of the statement used SUM(awayWin), etc , and added GROUP by team at the very end. This gave me the results I was looking for.
Thank you so much for your help. At this point UNION may be the way to go on this one, even though you recommend that is not be used if at all possible.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

COANetworkCommented:
I must have missed it where you said you were running MySQL - my code is valid for MS SQL Server.  It may have had a problem with the SELECT ... INTO concept.  But in any RDBMS - UNION is bad news.
0
PortletPaulfreelancerCommented:
please try this:
SELECT
      COALESCE(teamHome, teamAway) AS team
    , SUM(homeWin + awayWin)       AS win
    , SUM(homeTie + awayTie)       AS tie
    , SUM(homeLoss + awayLoss)     AS loss
    , SUM(homeWin + awayWin) * 2
      + SUM(homeTie + awayTie)     AS points

FROM `games`

WHERE leagueId=1
AND divId=3
AND roundId=1
AND date='2013-09-18'

GROUP BY COALESCE(teamHome, teamAway)
;	

Open in new window

NB: if any of those numeric fields might be NULL then inside those SUM()s please add IFNULL() e.g.
 , SUM(IFNULL(homeWin,0) + IFNULL(awayWin,0))       AS win
0
K207Author Commented:
Thanks so much for looking at this and for looking at this and for changing the topic to a more appropriate one.
I tried the COALESCE statement that you suggested, but unfortunately the results were quite strange in that it gave the win column the same end value as the loss column, and seemed to double the tie column wherever there was a tie.
team   win tie loss points
team1  2    0     2     4
team2  4    0     4      8
team3  3     2     2     8

I seem to have found a solution to the problem with the help of CAONetwork.
Again, thanks very much for the suggestion it is very much appreciated.
0
PortletPaulfreelancerCommented:
I believe CAONetwork also warned about using UNION :)

I had to make some assumptions about your data structure, so as follow-up I have actually tried to test that query - using my assumed data structure. It worked. with this result:
    |   TEAM | WIN | TIE | LOSS | POINTS |
    |--------|-----|-----|------|--------|
    | Team 1 |   1 |   1 |    2 |      3 |
    | Team 2 |   2 |   1 |    1 |      5 |
    | Team 3 |   1 |   2 |    1 |      4 |

Execution Plan for this:
ID SELECT_TYPE  TABLE TYPE POSSIBLE_KEYS KEY KEY_LEN REF ROW  FILTERED EXTRA
1   SIMPLE      games ALL                                3    100      Using where; Using temporary; Using filesort

Open in new window

Full data/query and result details:
**MySQL 5.6.6 m9 Schema Setup**:

    
    
    CREATE TABLE games
    	(`teamHome` varchar(6), `homeWin` int, `homeTie` int, `homeLoss` int, `teamAway` varchar(6), `awayWin` int, `awayTie` int, `awayLoss` int, `LeagueId` int, `divId` int, `roundId` int, `date` datetime)
    ;
    	
    INSERT INTO games
    	(`teamHome`, `homeWin`, `homeTie`, `homeLoss`, `teamAway`, `awayWin`, `awayTie`, `awayLoss`, `LeagueId`, `divId`, `roundId`, `date`)
    VALUES
    	('Team 1', 1, NULL, 1, 'Team 1', NULL, 1, 1, 1, 3, 1, '2013-09-18 00:00:00'),
    	('Team 2', NULL, 1, 1, 'Team 2', 2, NULL, NULL, 1, 3, 1, '2013-09-18 00:00:00'),
    	('Team 3', NULL, 2, NULL, 'Team 3', 1, NULL, 1, 1, 3, 1, '2013-09-18 00:00:00')
    ;

**Query 1**:

    SELECT
          COALESCE(teamHome, teamAway) AS team
        , SUM(IFNULL(homeWin,0) + IFNULL(awayWin,0))       AS win
        , SUM(IFNULL(homeTie,0) + IFNULL(awayTie,0))       AS tie
        , SUM(IFNULL(homeLoss,0) + IFNULL(awayLoss,0))     AS loss
        , SUM(IFNULL(homeWin,0) + IFNULL(awayWin,0)) * 2
          + SUM(IFNULL(homeTie,0) + IFNULL(awayTie,0))     AS points
    
    FROM `games`
    
    WHERE leagueId=1
    AND divId=3
    AND roundId=1
    AND date='2013-09-18'
    
    GROUP BY COALESCE(teamHome, teamAway)
    
    
    /* Expected results
    team     win     tie     loss  points
    Team1    1        1        2       3
    Team2    2        1        1       5
    Team3    1        2        1       4
    */
    

**[Results][2]**:
    
    |   TEAM | WIN | TIE | LOSS | POINTS |
    |--------|-----|-----|------|--------|
    | Team 1 |   1 |   1 |    2 |      3 |
    | Team 2 |   2 |   1 |    1 |      5 |
    | Team 3 |   1 |   2 |    1 |      4 |



  [1]: http://sqlfiddle.com/#!9/f565f/2

  [2]: http://sqlfiddle.com/#!9/f565f/2/0

Open in new window

0
PortletPaulfreelancerCommented:
the messages are:

Avoid UNION if you can - and you should be able to here

Providing sample data helps to ensure we don't make false assumptions about the data

If you would like to add some real sample data from your table I can show you how to avoid the union.

ideally also providing the DDL for the table games would assist.
In future questions you might want to include those items (DDL & sample data)
along with expected result.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.