MageeWorld MageeWorld - 5 months ago 7
SQL Question

How do I calculate the average when the numbers are grouped

I'm building (in Rails) an app to scrape NBA data (mostly for my own edification and a learning tool). I started with postgresql from scratch because it just makes more sense if I'm going to host with it. The database schema is set up exactly as I want it here.

Participant instances are based on a TEAM concept
Statistic instances are based on a PLAYER concept and thus many instances of Statistic make up the 'totals' for participant.

The following query:

Select SUM(steals) from statistics
INNER JOIN participants on statistics.participant_id = participants.id
INNER JOIN teams on participants.team_id = teams.id
WHERE teams.id = 15
GROUP BY statistics.participant_id;


yields (in the psql terminal app)

sum
-----
8
7
5
7
6
7
10
6
7
6
7
5
9
11
4
7
8


How do I write the sql query to calculate the average based on these 17 rows

(edit to simplify query and only ask one question)

Answer

Use the following query to wrap your query

SELECT sum, AVG(sum)
FROM (
  -- your query here
) x

on some systems you can also use a windowing function which does not require the outer query. The syntax for that looks like this

AVG(sum) OVER () as avg

since the average you are looking for is the result of a grouping you can't use it here.