CODEWITHSUNDEEP
sqlgroup-bywindow-functionsquerying
Shanice Khoo
Shanice Khoo

Reputation: 15

Why do I get different results in these 2 SQL queries? Using AVG alone vs. when using over()

Using AVG():

SELECT
    c.id,
    c.name,
    m.season,
    m.home_goal,
    m.away_goal,
    AVG(m.home_goal + m.away_goal) AS overall_avg
FROM 
    country AS c
LEFT JOIN 
    match AS m ON c.id = m.country_id
WHERE 
    name = 'Belgium'
GROUP BY 
    c.id, m.season, m.home_goal, m.away_goal

Using OVER():

SELECT
    c.id,
    c.name,
    m.season,
    m.home_goal,
    m.away_goal,
    AVG(m.home_goal + m.away_goal) OVER() AS overall_avg
FROM 
    country AS c
LEFT JOIN 
    match AS m ON c.id = m.country_id

I get different results for the average column. I don't understand what the difference is.

Upvotes: 0

Views: 153

Answers (2)

Stu
Stu

Reputation: 32599

Ignoring the absence of the where criteria in your second query (just a mistake in your question?), they are fundamentally different.

By using the group by clause, the results are grouped by the distinct unique combinations of the grouped columns, collapasing all like-rows into one, and for each of these groups, the avg() - and any other aggregated functions also - operate on the rows in these groups individually.

In the second query, no grouping of rows occurs and so every row is returned. The use of the avg() function with an over clause is referred to as a window function, where the window in question (basically a qualifying range of rows) is specified by the over clause; () denotes the window is the entire result set and each row gets the average of all rows.

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

In your first GROUP BY query, AVG(m.home_goal + m.away_goal) is an aggregate function, which will return the average for each group. In the second non GROUP BY query, AVG(m.home_goal + m.away_goal) OVER() is a window function which will return the average over the entire table.

Upvotes: 2

Related Questions