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

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.

Solution

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.