SQL Group By and Having Command

    3 Votes

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.

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.

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.

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.