Web Analytics Made Easy - Statcounter

Technology with Vivek Johari

SQL Basic SQL Server

Group by…..Having Clause

Byvivekjohari

Jan 31, 2010 #SQL Basic Concepts

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:-

Trending
Exception handling in SQL Server

 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.

DMCA.com

Discover more from Technology with Vivek Johari

Subscribe to get the latest posts sent to your email.

By vivekjohari

I am currently working as a Senior Database Professional and have around 18 years of experience in database. Degree:- Master Degree in Computer(MCA) Certification course in Data Science & Machine Learning from Indian Institute of Technology (IIT), Delhi Work experience:- Designing of the database. Database Optimization. Writing Complex Stored Procedures,Functions,Triggers etc. Designing and developing SSIS & DTS packages. Designing SQL Reports using SSRS. Database Server Maintenance. Certification:- MCTS: DA-100: Analysing Data with Microsoft Power BI MCTS: DP-300: Administering Relational Databases on Microsoft Azure Microsoft certified Sql DBA in Sql server 2008 (MCTS). Microsoft certified BI professional in Sql server 2008 (MCTS). Oracle certified profession DBA in ORACLE 10g (OCP) certified profession DBA in ORACLE 9i (OCP) My other publication Technical Blog:- Technologies with Vivek Johari Guest Author and Blogger at sqlservercentral.com

Related Post

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

Jan 1, 2025 vivekjohari
SQL Server

Database Partitioning in SQL Server

Dec 31, 2024 vivekjohari
SQL Server

What are the important python libraries?

Dec 31, 2024 vivekjohari
71 thought on “Group by…..Having Clause”

  4. … [Trackback]

    […] Find More Information here to that Topic: techmixing.com/2010/01/group-byhaving-clause.html […]

    Reply

Leave a Reply

You missed

Miscellaneous SQL Server

🎉✨ Happy New Year 2025! ✨🎉

January 1, 2025 vivekjohari No Comments
SQL Server

Database Partitioning in SQL Server

December 31, 2024 vivekjohari No Comments
SQL Server

What are the important python libraries?

December 31, 2024 vivekjohari No Comments
SQL Server

Understanding Estimated and Actual Query Plans in SQL Server

December 31, 2024 vivekjohari No Comments

Discover more from Technology with Vivek Johari

Subscribe now to keep reading and get access to the full archive.

Continue reading