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