SQL Server - SQL GROUP BY, HAVING, EXIST Statements

 

GROUP BY :

GROUP BY statement is used to group all rows which has same value. GROUP BY is used with functions COUNT(), MIN(), MAX(), AVG(), SUM().

Example:

1.    SELECT Sum(Salary)

              FROM Employee

               GROUP BY Dept_Id;

 

2.    SELECT Avg(Salary)

   FROM Employee;

               GROUP BY Dept_Id;

 

3.    SELECT Department.Dept_Id,

            Department.Dept_Name,

            Avg(Employee.Salary)            

               FROM Employee

               LEFT JOIN Department

               ON Employee.Dept_Id = Department.Dept_Id

               GROUP BY Department.Dept_Id;

 

HAVING :

HAVING statement is used to set FILTER result set of GROUP BY.

Example:

               SELECT Sum(Salary)

              FROM Employee

               GROUP BY Dept_Id;

               HAVING Count(Dept_Id) > 5;

 

EXISTS :

EXISTS operator is used to test existence of any particular record in sub record set. EXITS operator return value TRUE if Sub query return one or more records.

Example:

SELECT   Employee.Name

FROM Employee

WHERE EXISTS (SELECT Dept_Name FROM Department

                               WHERE Employee.Dept_Id = Department.Dept_Id);

 

NOT EXISTS :

NOT EXISTS operator is used to test NON existence of any particular record in sub record set. NOT EXITS operator return value TRUE if Sub query return ZERO/NO records.

Example:

SELECT   Employee.Name

FROM Employee

WHERE NOT EXISTS (SELECT Dept_Name FROM Department

                               WHERE Employee.Dept_Id = Department.Dept_Id);