Having Sums, Averages, and Other Grouped Data

When you strive to get an  average 

1. The business requirement is what is current average salary for all Employees.

select AVG(salary) from employee;

  The avg aggregate function sum up the salary values and then divides it by total number of employee

  records those doesn't have the NULL salary .

  Thus the avg function ignores the NULL values 

  To get the business required answer substitute the Non NULL value for NULL value.

   
select AVG(NVL(salary,0)) from employee;

  This will return the exact average salary of all employee.

The Difference between Count(*) and  Count(Coulmn_name) 

 The count (*) return the all the records which satisfy the  query condition and count(*)  does not ignore  the null value, however the count(Column_name)  ignores the null records from the count.


Categorization and aggregation  of data.

  The group by clause enables  you to collect the data from multiple records and tclub it by one or more columns.
 The Aggregate function and the group by clause used to tandem  to determine the aggregate value for every group.

 count of employees in each department

select COUNT(employee_id), department_id
    from employee
    GROUP BY department_id
    ORDER BY department_id;

When the group by is followed by the order by then the clumn listed in the order should be listed in the select  , otherwise it will flag an error message.
  similarly  if the column listed in the group by should be listed in the select.

--,ASC, DESC, NULLS FIRST, and NULLS LAST options behave and how null values are handled by default in an ORDER BY clause


HAVING the last word   
   
 Just like the select list can use the where clause to filter records from the result set those satisfying the condition 
  mentioned in the where clause , similarly to filter the result of group by clause (Categorized data)   the having function is used .