CODEWITHSUNDEEP
sqlpostgresql
Dragan R.
Dragan R.

Reputation: 670

Calculating and using column average from a subquery

I have a database of team statistics, and I would like to normalize the sum of a certain stat. For example, I have a table of redundant records like this:

team_name | team_code | player | steal | block
Team A    | TA        | PA     | 100   | 100
Team A    | TA        | PA     | 100   | 100
Team A    | TA        | PA     | 100   | 100
Team B    | TB        | PB     | 200   | 200
Team C    | TC        | PC     | 300   | 300
Team C    | TC        | PC     | 300   | 300

I would like to get the team's (steals + blocks) across all players, divided by the mean (steal + block) of all teams.

For example, I need an output to look like this (Note: I added in the expression I need for norm_def_total):

team_name | team_code | steal_sum | block_sum | def_total | norm_def_total
Team A    | TA        | 300       | 300       | 600       | 600/(avg(def_total))
Team B    | TB        | 200       | 200       | 400       | 400/(avg(def_total))
Team C    | TC        | 600       | 600       | 1200      | 1200/(avg(def_total))

Here is my attempt:

SELECT b.team_name, b.team_code, b.def_total/AVG(b.def_total) normalized_def FROM
(
    SELECT  team_name,
        team_code,
        SUM(steal) steal_sum,
        SUM(block) block_sum,
        CAST(SUM(steal) + SUM(block) AS double precision) def_total
    FROM gamelog
    WHERE team_name IN (SELECT DISTINCT (team_name) FROM gamelog)
    GROUP BY team_name,
             team_code
    ORDER BY def_total DESC
) b
GROUP BY
    b.team_name,
    b.team_code,
    b.def_total

From above, my problem is that b.def_total/AVG(b.def_total) always returns 1. I think it is taking the AVG just for that particular team, but I want the AVG for all teams from b. How can I get the actual average from b to be used in the calculation?

Upvotes: 0

Views: 1378

Answers (2)

SMA
SMA

Reputation: 37023

Try something like:

SELECT team_name, 
       team_code, 
       sum(steal) as steal_sum, 
       sum(block) AS block_sum, 
       sum(steal) + sum(block) AS def_total, 
       ((sum(steal) + sum(block)) / sum(steal) + sum(block)) / count(*) AS norm_def_total
FROM   gamelog
GROUP BY team_name, team_code

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93704

Try this.

SELECT b.team_name,
       b.team_code,
       Sum(def_total) / Avg(b.def_total)OVER(partition BY NULL)
FROM   (SELECT team_name,
               team_code,
               Sum(steal)                                        steal_sum,
               Sum(block)                                        block_sum,
               Cast(Sum(steal) + Sum(block) AS DOUBLE PRECISION) def_total
        FROM   gamelog
        WHERE  team_name IN (SELECT DISTINCT ( team_name )
                             FROM   gamelog)
        GROUP  BY team_name,
                  team_code
        ORDER  BY def_total DESC) b
GROUP  BY b.team_name,
          b.team_code

Upvotes: 3

Related Questions