CODEWITHSUNDEEP
sqlpostgresql
Nick
Nick

Reputation: 1268

SQL FROM clause missing error

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.

Upvotes: 0

Views: 228

Answers (2)

shawnt00
shawnt00

Reputation: 17935

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).

Upvotes: 1

xQbert
xQbert

Reputation: 35343

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.

Upvotes: 5

Related Questions