Okay, let's get started with OVER().
OVER(...) changes based on what you put inside the brackets. At its most basic, the brackets are left empty, like this:
OVER(). This means that the window consists of all the rows in the query. Take a look at the example below:
AVG(Salary) OVER() AS AvgSalary
It's pretty easy to understand, but take a look at let's focus on the last column:
What might this mean?
AVG(Salary) means we're looking for the average salary. What rows are we including in this function? All of them, because
OVER() with empty brackets means "for all rows in the query result". In other words, we're looking for the average salary in the entire company.
Note that we did NOT group rows.
OVER() allows us to show the details of single rows as well as the result of the aggregating function. That wouldn't be so easy with
GROUP BY – we would have to write a subquery, which is more complicated and less effective.
OVER() makes our work simple and efficient.