SQL Aggregate Functions

In this post, we will discuss on the Aggregate Functions on SQL. To show results, we will use the following table.  Lets start -
Table - Workers ( Its a replica of Employees Table )

• MIN , MAX & AVG - These are simple functions, used to fetch minimum ( MIN ), maximum (MAX) and average of the elements. These are very simple to understand. Look at the results -

• Count(column_name ) , Count(*), count(1) - These are pretty simple. Count(*) will return the Total  Number of Rows in the table. 'Count' will return the total number of values present in that column.

Note -  Questions  - What will happen if you want to return the number of Column Values, containing  NULLS. - Well, Count will not consider the 'Null' values into account.
"Count" will always give you the Numbers of Non-Null values.
Look at the results -
What about Count(1) or Count(0), or may be Count(2) :) - Now, before proceeding, i would like to note down following points to avoid misconception.

1. Count (1) doesn't mean the 'first' column in the SQL Query. It's just an expression.
2. Count (* ) doesn't mean the same as 'Select *", following that 'all things gets loaded..", no, it's also an expression.
3. Count(any_expression) returns all non-null count, from the object. Hence, count(*), count(1) or count(0) , all the just expressions, and will give you the same result.
4. Performance wise, now-a-days, they give the same result. In earlier versions of Oracle, it might had been different, but that is also due to Oracle optimizer.

You can also check 'Execution Plan' of all these Count (s).
All screenshots present -


Questions  -  Write a SQL Query to get the Name of the Employees, who gets Maximum Salary from Each Department - Very Common Question -- Lets see the Query below -

I could have used "Exists" instead of "IN", will discuss this in "Performance Tuning".
Questions - Write an SQL Query to find out the Name of the Employee, who gets Maximum of all the Average Salary, for Each Department -  "You should not try to group by Max(avg(salary)) ", because it will lead to the error !!
Here is the query -