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