Understand over() clause in comparison with group by and equivalent code [using inner join and subquery].

Over()

It is a similar thing to group by but instead of shrinking the table it initializes the value in front of each column & gives its results in front of every row..
Group by only gives aggregated columns, other columns are not shown while we use group by. It gives an error if we select other columns.

The query which gives error is:

//gives error
select name,
gender,
count(gender)
from employee group by gender;

We all know that we will use over() to remove this error but we can also solve this using another by using inner join and subquery.

The query is:

//using subquery and inner join
select name,
employee.gender,
salary,
genders.gender_total
from employee
inner join
(select gender,
count(gender) as gender_total
from employee 
group by gender) as genders
on employee.gender=genders.gender

The output we get is:

In this query, we have put the query as subquery which was giving an error & performed inner join with employee table on gender column to include other columns. So by this method, we can include other columns with aggregated columns while using group by.

But the query is big & requires a lot of brainstorming so equivalent to this which can minimalize the error is over()

The query using over() is:-

select name,
gender,
salary,
count(gender)
over(partition by gender) 
as gender_total from employee

This output of this query is the same as I did above in the inner join query. The output is as follows:-

The same thing with CTE[common table expression]

with  genders as(
select gender,count(gender) as gender_total from employee group by gender)
select name,employee.gender,salary,genders.gender_total from employee
inner join 
genders on employee.gender=genders.gender