Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Group by.....Having Clause

Group By:- Group By clauses is used to groups rows based on the distinct values of the specified columns.

The syntax of the Group by clause is:-
  Select column1, column2, column3, aggregate_function(expression ) 
           From TableName Where (condition) 
           Group By Column1, column2, column3

For Example, suppose we have a table named EmpInfo which contains the information about the Id of the employee and the Id of the project and it contains the following data

Table:- EmpInfo
EmployeeId  Projectid
--------------------------
1                          1
2                          2
3                          2
4                          2
5                          3

Now suppose we want to know about the number of employee belonging to each project then we can use the group by clause as given below:-

select Projectid , count(employeeid) as NumberOfEmployee  from  EmpInfo  Group by ProjectId

This query will give us the following result:-
ProjectId     NumberOfEmployee     
1                    1
2                    3
3                    1

Having Clause :- Having clause is used in conjunction with the group clause by imposing a condition on the group by clause to further filter the records return by the group by clause.

Syntax for Having Clause:-
       Select column1, column2, column3, aggregate_function(expression ) 
       From TableName Where (condition) 
       Group By Column1, column2, column3
       having aggregate_function(expression )  operator value

For example, suppose we want to know which project has more than 2 employee and the total no of their respective employees, we can use the following query

select Projectid , count(employeeid) as NumberOfEmployee  from  EmpInfo  Group by ProjectId having count(employeeid) >2


The query will give us the following result:-
 ProjectId  NumberOfEmployee
2                    3

In the above example, the Having clause further filter the result return by the Group By clause by imposing a condition that only those projectid will be shown who has more than 2 employee.



Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.