SQL | GROUP BY
The GROUP BY Statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has same values in different rows then it will arrange these rows in a group.
- GROUP BY clause is used with the SELECT statement.
- In the query, GROUP BY clause is placed after the WHERE clause.
- In the query, GROUP BY clause is placed before ORDER BY clause if used any.
SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG(). table_name: Name of the table. condition: Condition used.
- Group By single column: Group By single column means, to place all the rows with same value of only that particular column in one group. Consider the query as shown below:
SELECT NAME, SUM(SALARY) FROM Employee GROUP BY NAME;
The above query will produce the below output:
As should be obvious in the above yield, the columns with copy NAMEs are grouped under same NAME and their relating SALARY is the aggregate of the SALARY of copy lines. The SUM() function of SQL is utilized here to compute the aggregate.
Group By multiple columns:
Group by multiple column is say for example, GROUP BY column1, column2. This means to place all the rows with same values of both the columns column1 and column2 in one group. Consider the below query:
SELECT SUBJECT, YEAR, Count(*) FROM Student GROUP BY SUBJECT, YEAR;
As should be obvious in the above yield the understudies with both same SUBJECT and YEAR are put in same group. Also, those whose solitary SUBJECT is same yet not YEAR has a place with various groups. So here we have grouped the table as indicated by two columns or more than one section.
We realize that WHERE proviso is utilized to put conditions on columns yet imagine a scenario in which we need to put conditions on groups.
This is the place HAVING statement comes into utilization. We can utilize HAVING statement to put conditions to choose which group will be the piece of conclusive outcome set. Additionally we can not utilize the total functions like SUM(), COUNT() and so on with WHERE provision. So we need to utilize HAVING proviso in the event that we need to utilize any of these functions in the conditions.
SELECT column1, function_name(column2) FROM table_name WHERE condition GROUP BY column1, column2 HAVING condition ORDER BY column1, column2; function_name: Name of the function used for example, SUM() , AVG(). table_name: Name of the table. condition: Condition used.
SELECT NAME, SUM(SALARY) FROM Employee GROUP BY NAME HAVING SUM(SALARY)>3000;
As you can see in the above output only one group out of the three groups appears in the result-set as it is the only group where sum of SALARY is greater than 3000. So we have used HAVING clause here to place this condition as the condition is required to be placed on groups not columns.