How to iterate through a select within another select

Question

I have two tables Players(Name,Surname,Goals,TeamID) and Teams(Name,TeamID).

In the following query I get the average goals' number per team and select the players who have a number of goals greater than the average.

SELECT Players.Name, Players.Surname, Teams.Name, Players.Goals, (SELECT ROUND(AVG(Players.Goals)) FROM Players GROUP BY Players.TeamID) AS TeamAverage
FROM Players
JOIN Teams ON Players.TeamID = Teams.TeamID
WHERE Players.Goals > (SELECT ROUND(AVG(Players.Goals))
                          FROM Players
                          GROUP BY Players.TeamID)

In the TeamAverage column I want to display the average goals' number per team, but the query only gets the average number of goals for the first team.

Who can I iterate through this select

(SELECT ROUND(AVG(Players.Goals)) FROM Players GROUP BY Players.TeamID)

and get each result according to the teams' ID?

i'd have a temp table to store the averages per team, then you can just do a join on that — austin wernli, Jan 10 '18 at 16:52
@austinwernli I'd like not to use another table. Is there another solution? — grrigore, Jan 10 '18 at 16:55
Is the goal to find players who has more goals than any teams average? Or to compare their goals against each team's average? — Schwern, Jan 10 '18 at 17:08
@Schwern The goal is to also display the teams' average into a column (*TeamAverage*) for each player. Of course, it's about the team's average corresponding to each player's team. — grrigore, Jan 10 '18 at 17:13
@grrigore of course there are other solutions like the idea you posted about sub query instead of using temp tables. However, i feel as though temp tables can make it more readable as to what the query is doing. Also read this to find the difference in performance between subquery and temp table https://stackoverflow.com/questions/16767645/why-is-there-a-huge-performance-difference-between-temp-table-and-subselect — austin wernli, Jan 10 '18 at 17:34

score 2 · Answer 1 · answered Jan 10 '18 at 17:03

By using the query in the parentheses you are creating a sub query that gets evaluated first and independently from the row in every row. If this is not part of a view and a query that needs to be run as part of a data collection or visualization, then a temp table would work fine by itself or in a stored procedure. If it needs to be in a view, then using a join into a subquery would work.

Using a temp table:

SELECT Players.TeamID as TeamID
       ,ROUND(AVG(Players.Goals)) as PlayerAverage
INTO #temp
FROM Players
Group BY Players.TeamID

SELECT tea.Players.Name
    ,tea.Players.Surname
    ,tea.Teams.Name
    ,tea.Players.Goals
    ,t.PlayerAverage
FROM Players
JOIN Teams tea ON Players.TeamID = Teams.TeamID
left join #temp t on Players.TeamID = t.TeamID
WHERE Players.Goals > t.PlayerAverage

using a joined subquery:

SELECT tea.Players.Name
    ,tea.Players.Surname
    ,tea.Teams.Name
    ,tea.Players.Goals
    ,t.PlayerAverage
FROM Players
JOIN Teams tea ON Players.TeamID = Teams.TeamID
left join (SELECT Players.TeamID as TeamID
       ,ROUND(AVG(Players.Goals)) as PlayerAverage
FROM Players
Group BY Players.TeamID) t on Players.TeamID = t.TeamID
WHERE Players.Goals > t.PlayerAverage

Hope this helps!

score 2 · Accepted Answer · answered Jan 10 '18 at 17:16

Try this, I'd personally have gone down the route of using a temp table but this should also work for you. I gave your main tables an alias of P and T to help your sub query know what to compare against:

SELECT
    P.Name,
    P.Surname,
    T.Name,
    P.Goals,
    (SELECT ROUND(AVG(Players.Goals)) FROM Players WHERE Players.TeamID = P.TeamID) AS TeamAverage
FROM
    Players P
    JOIN Teams T ON P.TeamID = T.TeamID
WHERE
    P.Goals > (SELECT ROUND(AVG(Players.Goals))
                          FROM Players
                          WHERE Players.TeamID = P.TeamID)

score 1 · Answer 3 · answered Jan 10 '18 at 17:13

You could do something like this using a temp table to help store the results for teamAverage

CREATE TABLE #players (
    goals INT,
    NAME NVARCHAR(200),
    surname NVARCHAR(200),
    teamid INT
    )

CREATE TABLE #teams (
    NAME NVARCHAR(200),
    teamid INT
    )

INSERT INTO #teams (
    NAME,
    teamid
    )
VALUES 
('team1',1),
('team2',2),
('team3',3)

INSERT INTO #players (
    goals,
    NAME,
    surname,
    teamid
    )
VALUES 
(10,'bob','bob',1),
(2,'bob1','bob',1),
(5,'bob2','bob',1),
(1,'bob3','bob',3),
(3,'bob4','bob',3),
(2,'bob5','bob',3),
(1,'bob6','bob',2),
(2,'bob7','bob',2)

--Create temp table to store team average and teamid
SELECT round(avg(p.Goals), 2) AS average,
    t.teamid
INTO #TeamAverages
FROM #players p
LEFT JOIN #teams t
    ON p.teamid = t.teamid
GROUP BY t.TeamID

--join on above table and compare to team average table
SELECT p.NAME AS playerName,
    p.Surname,
    t.NAME AS teamName,
    p.Goals,
    ta.average
FROM #players p
LEFT JOIN #teams t
    ON p.TeamID = t.TeamID
LEFT JOIN #TeamAverages ta
    ON ta.teamid = t.teamid
WHERE p.Goals > ta.average

DROP TABLE #players

DROP TABLE #TeamAverages

DROP TABLE #teams

How to iterate through a select within another select

3 Answers3