SQL Group By and Having Command

Learning SQL won't be complete without knowing the usage of "Group By" and "Having" clause. These commands are one of the most commonly used one is SQL queries and knowing when and how to use them make your life easy.

SQL Group By Clause

SQL Group by Clause is used to find count, sum, min and max values belonging to a particular category. For example, to get total salary of employees from the Salary table given below, following SQL query is used.

Employee_Name	Year	Month	Salary
John	2015	April	30000
John	2015	May	31000
John	2015	June	32000
Alex	2015	April	40000
Alex	2015	May	41000
Alex	2015	June	42000

select employee_name,Sum(salary) Salary from salary group by employee_name

The result of the above query is given below. The group by function helps us to add up the salaries with respect to the column specified after "Group by" function. In this case, it is employee name.

Employee_Name	Salary
John	93000
Alex	123000

SQL Having

To filter out data produced by group by function, having command is used.  Note that, having keyword should be used after group by. To fetch employees whose total salary is greater than 1,00,000 from salary table, following SQL query is used.

select employee_name,Sum(salary) Salary from salary group by employee_name having Sum(salary) > 100000

In the previous query using group by function, we got 2 records as result. When having clause is used on the above result, only one record meets the condition specified in the having clause. Result of the SQL Query using group by and having clause is given below.

Employee_Name	Salary
Alex	123000

The "where" clause is used to filter data from a table, but we can't fetch data based on an aggregate condition. As shown in the above query, if we want to fetch the names of employees whose salary to date exceeds a certain amount or monthly incentives reach a certain limit, we need to use "Having" command.