SQL-Intersecting nested queries

Question

Using Oracle SQL, I'm trying to write a statement that answers the following question: NFL Teams with more wins than their division average that also have more penalty yards than the division average.

I have two separate statements that are able to return each individually (IE: a table that lists teams with more wins than division average, and a separate table that lists teams with more penalty yards than average)

I am running into errors when I try and intersect the two queries however. 'Missing select keyword' is the given error.

WITH divisionPenaltyYards AS 
(
    SELECT division
        ,avg(penaltyyards) AS AVGPenalty
    FROM nfl.teams
    GROUP BY division
)
WITH divisionWins AS 
(
        SELECT division
            ,avg(wins) AS AVGWins
        FROM nfl.teams
        GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionPenaltyYards 
    ON nfl.teams.division = divisionPenaltyYards.division
WHERE penaltyyards > AVGPenalty

INTERSECT

SELECT team
FROM nfl.teams
INNER JOIN divisionWins 
    ON nfl.teams.division = divisionWins.division
WHERE wins > AVGWins;

EDIT: More info per request

NFL.Teams consists of stats for all the teams in the NFL. Team, division, wins, penaltyyards...

An example of the data set would include Packers, NFC NORTH, 6, 984 Steelers, AFC NORTH, 12, 817 and so on.

Desired result would be a table listing all teams that have more wins than the division average, while also having more penalty yards than the division average.

Sample data and desired results would really help other people figure out what you are attempting to do. — Gordon Linoff
– Gordon Linoff, Commented Sep 18, 2018 at 20:48

JNevill · Accepted Answer · 2018-09-18 21:32:52Z

2

The second CTE should not begin with the WITH keyword. Just , divisionWins AS

WITH divisionPenaltyYards AS 
(
    SELECT division
        ,avg(penaltyyards) AS AVGPenalty
    FROM nfl.teams
    GROUP BY division
)
, divisionWins AS 
(
        SELECT division
            ,avg(wins) AS AVGWins
        FROM nfl.teams
        GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionPenaltyYards 
    ON nfl.teams.division = divisionPenaltyYards.division
WHERE penaltyyards > AVGPenalty

INTERSECT

SELECT team
FROM nfl.teams
INNER JOIN divisionWins 
    ON nfl.teams.division = divisionWins.division
WHERE wins > AVGWins;

There is also no need to use two CTEs here.

WITH divisionCTE AS 
(
    SELECT division, 
        avg(penaltyyards) as avgpenalty, 
        avg(wins) as avgwins 
    FROM nfl.teams 
    GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE penaltyyards > AVGPenalty

INTERSECT

SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE wins > AVGWins;

Furthermore, INTERSECT works here, but UNION would make more sense. Ultimately though, neither are needed:

WITH divisionCTE AS 
(
    SELECT division, 
        avg(penaltyyards) as avgpenalty, 
        avg(wins) as avgwins 
    FROM nfl.teams 
    GROUP BY division
)
SELECT team
FROM nfl.teams
INNER JOIN divisionCTE ON nfl.teams.division = divisionCTE.division
WHERE penaltyyards > AVGPenalty
    AND wins > avgwins

Lastly, if you wanted to avoid the JOIN you could use window functions:

SELECT team
FROM 
    (
        SELECT team, wins, penaltyyards,
            avg(penaltyyards) OVER (PARTITION BY division) as avgpenalty, 
            avg(wins) OVER (PARTITION BY division) as avgwins 
        FROM nfl.teams 
    ) averages
WHERE penaltyyards > AVGPenalty AND wins > avgwins

edited Sep 18, 2018 at 21:32

answered Sep 18, 2018 at 21:21

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

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

2 Comments

Justiciar Over a year ago

Great answer, and I appreciate your input. Anything that makes it work with less code is great. 1 minor note, you forgot to change the name of DivisionWins during the join statement to divisionCTE when you renamed the CTE. Otherwise, this worked flawlessly.

JNevill Over a year ago

Ah! I certainly did. I have fixed that and added one more option using window functions to perform the averages so that the JOIN can be avoided. Not sure if it will be faster as I'm betting your data is relatively small, but it does make a nice succinct query

Collectives™ on Stack Overflow

SQL-Intersecting nested queries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related