SQL GROUP BY – javatpoint

next →
← prev

Mục Lục

SQL group by

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:

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:

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:

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:

Example:

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.

Next Topic

SQL Tutorial

← prev
next →

Alternate Text Gọi ngay