Javatpoint Logo
Javatpoint Logo

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 TopicSQL Tutorial




Youtube For Videos Join Our Youtube Channel: Join Now

Help Others, Please Share

facebook twitter pinterest

Learn Latest Tutorials


Preparation


Trending Technologies


B.Tech / MCA