In SQL, The Group By statement is used for organizing similar data into groups. The data is further organized with the help of equivalent function. It means, if different rows in a precise column have the same values, it will arrange those rows in a group.
- The SELECT statement is used with the GROUP BY clause in the SQL query.
- WHERE clause is placed before the GROUP BY clause in SQL.
- ORDER BY clause is placed after the GROUP BY clause in SQL.
Syntax:
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;
function_name: Table name.
Condition: which we used. Sample Table:
Employee
| S.no | Name | AGE | Salary |
|---|---|---|---|
| 1 | John | 24 | 25000 |
| 2 | Nick | 22 | 22000 |
| 3 | Amara | 25 | 15000 |
| 4 | Nick | 22 | 22000 |
| 5 | John | 24 | 25000 |
Student
| SUBJECT | YEAR | NAME |
|---|---|---|
| C language | 2 | John |
| C language | 2 | Ginny |
| C language | 2 | Jasmeen |
| C language | 3 | Nick |
| C language | 3 | Amara |
| Java | 1 | Sifa |
| Java | 1 | dolly |
Example:
Group By single column: Group By single column is used to place all the rows with the same value. These values are of that specified column in one group. It signifies that all rows will put an equal amount through a single column, which is of one appropriate column in one group.
Consider the below query:
SELECT NAME, SUM (SALARY) FROM Employee
GROUP BY NAME; The output of the query is:
| NAME | SALARY |
|---|---|
| John | 50000 |
| Nick | 44000 |
| Amara | 15000 |
In the output, the rows which hold duplicate NAME are grouped under a similar NAME, and their corresponding SALARY is the sum of the SALARY of the duplicate rows.
- Groups based on several columns: A group of some columns are GROUP BY column 1, column2, etc. Here, we are placing all rows in a group with the similar values of both column 1 and column 2.
Consider the below query:
SELECT SUBJECT, YEAR, Count (*)
FROM Student
Group BY SUBJECT, YEAR; Output:
| SUBJECT | YEAR | Count |
|---|---|---|
| C language | 2 | 3 |
| C language | 3 | 2 |
| Java | 1 | 2 |
In the above output, the student with similar SUBJECT and YEAR are grouped in the same place. The students who have only one thing in common belongs to different groups. For example, if the NAME is same and the YEAR is different.
Now, we have to group the table according to more than one column or two columns.
HAVING Clause
WHERE clause is used for deciding purpose. It is used to place conditions on the columns to determine the part of the last result-set of the group. Here, we are not required to use the combined functions like COUNT (), SUM (), etc. with the WHERE clause. After that, we need to use a HAVING clause.
Having clause Syntax:
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;
function_name: Mainly used for name of the function, SUM(), AVG().
table_name: Used for name of the table.
condition: Condition used. Example:
SELECT NAME, SUM(SALARY) FROM Employee
GROUP BY NAME
HAVING SUM(SALARY)>23000; Output:
| Name | SUM(SALARY) |
|---|---|
| John | 50000 |
According to the above output, only one name in the NAME column has been listed in the result because there is only one data in the database whose sum of salary is more than 50000.
It should be placed on groups, not on the columns.
Points:
- The GROUP BY Clause is used to group the rows, which have the same values.
- The SELECT statement in SQL is used with the GROUP BY clause.
- In the Group BY clause, the SELECT statement can use constants, aggregate functions, expressions, and column names.
- The GROUP BY Clause is called when the HAVING clause is used to reduce the results.
Leave a Reply