asked on

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

ASKER CERTIFIED SOLUTION

COANetwork

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

K207

ASKER

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

K207

ASKER

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.

COANetwork

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.

PortletPaul

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

K207

ASKER

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.

PortletPaul

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

PortletPaul

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.