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