SQL FROM clause missing error

Question

I just started learning SQL the other day and hit a stumbling block. I've got some code that looks like this:

SELECT player, SUM(wins) from (
    SELECT win_counts.player, win_counts.wins
    from win_counts
    UNION
    SELECT lose_counts.player, lose_counts.loses
    from lose_counts
    group by win_counts.player
) as temp_alias

Here is the error I get:

ERROR: missing FROM-clause entry for table "win_counts" LINE 7: group by win_counts.player

This win_counts table contains a list of player ids and the number of matches they have one. The lose_counts tables contains a list of player ids and the number of matches they have lost. Ultimately I want a table of player ids and the total number of matches each player has played.

Thank you for the help. Sorry I don't have more information... my understanding of sql is pretty rudimentary.

UNION is definitely wrong for this query It should be UNION ALL. — Gordon Linoff
– Gordon Linoff, Commented Dec 17, 2015 at 19:11
You are right! I had it that way before and was messing with the syntax since I wasn't sure what was correct — Nick
– Nick, Commented Dec 17, 2015 at 19:17

xQbert · Accepted Answer · 2015-12-17 19:29:05Z

5

Group by appears to be in the wrong place.

SELECT player, SUM(wins) as SumWinsLoses
FROM(
    SELECT win_counts.player, win_counts.wins
    FROM win_counts

    UNION ALL -- as Gordon points out 'ALL' is likely needed, otherwise your sum will be 
              -- off as the UNION alone will distinct the values before the sum
              -- and if you have a player with the same wins and losses (2), 
              -- the sum will return only 2 instead of (4).
    SELECT lose_counts.player, lose_counts.loses
    FROM lose_counts) as temp_alias
 GROUP BY player

Just so we are clear though the SUm(Wins) will sum wins and losses as "wins" the first name in a union for a field is the name used. So a players wins and losses are going to be aggregated.

Here's a working SQL FIddle Notice without the union all... the player #2 has an improper count.

edited Dec 17, 2015 at 19:29

answered Dec 17, 2015 at 19:07

xQbert

35.5k2 gold badges46 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

shawnt00 Over a year ago

Does postgresql allow for this syntax: as temp_alias(player, win_loss_count)?

Nick Over a year ago

That was the issue :)

xQbert Over a year ago

@shawnt00 I don't follow. SQL fiddle seems to work, So I don't understand your comment

shawnt00 Over a year ago

It was just a question. I was think about helping OP see other ways to control the named of columns.

xQbert Over a year ago

Ah Now I see thanks! I for some reason always alias inside on the first select of the union and forget about that method.

shawnt00 · Accepted Answer · 2015-12-17 23:31:46Z

You already have a good answer and comments from others. For your edification:

In some scenarios it might be more efficient to aggregate before the union.

select player, sum(wins) from (
    select player, count(*) as wins
    from win_counts
    group by player
    UNION ALL /* without ALL you'll eliminate duplicate rows */
    select player, count(*) as losses
    from lose_counts
    group by player
) as t
group by player

This should also give equivalent results if each player has both wins and losses:

select wins.player, wins + losses as total_matches
from
    (
        select player, count(*) as wins
        from win_counts
        group by player
    ) as wins
    inner join
    (
        select player, count(*) as losses
        from lose_counts
        group by player
    ) as losses
        on losses.player = wins.player

The fix for missing wins/losses is a full outer join:

select
    coalesce(wins.player, losses.player) as player,
    coalesce(wins. 0) + coalesce(losses, 0) as total_matches
from
    (
        select player, count(*) as wins
        from win_counts
        group by player
    ) as wins
    full outer join
    (
        select player, count(*) as losses
        from lose_counts
        group by player
    ) as losses
        on losses.player = wins.player

These complicated queries should give you a taste of why it's a bad idea to use separate tables for data that belongs together. In this case you should probably prefer a single table that records all matches as wins, losses (or ties).

Collectives™ on Stack Overflow

SQL FROM clause missing error

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related