The SQL Group by clause

  • The SQL Group by clause, as the name suggests, groups the similar column values, performs some aggregated function (like sum, count) and returns the consolidated result in groups.
  • The group by clause works with the aggregate functions like sum, count, min, max etc.
  • The result set returned is in the form of groups based on the given column(s).
  • The group by clause is used with the SQL select statement.
  • The group by in SQL follows the Where clause (if included in the select statement).
  • The group by precedes the Order by clause.
Group by example with Sum
Group by example with count

To make it clearer, consider this scenario where a table is storing the employee’s salaries paid every month.

You are required to see how much salary has been paid till today to each employee? How can you do that?

The one way can be bringing the results of each employee, by using the simple select statement and calculate the sum by yourself. You can also sum up the whole employee_salary column, by using the sum function, but it will return all employees summed data.

Keep this scenario in mind and let us show you how Group by SQL clause makes it quite easier!

Syntax of Group by clause

The general syntax of the group by clause is:

Select col1, col2, aggregate_function(column_name) from table_name

Where col1= val1            

Group by column_name

Order by column_name

Examples of using group by clause

In this example, we will use the tbl_emp_salary_paid table that stores the employee’s salaries. So the salaries paid every month to employees are stored in this table.

Group by example with SUM function

This query will return total salary amount paid, overall to each employee.

The SQL sum with the group by query:

See Group by with sum example online

As you can see in the above graphic, two queries are shown. One with a simple select statement to show what table contains. The other query uses the group by with the sum function to group each employee’s total salary paid.

Group by example with Min function

This query will return minimum salary paid to each employee.

The group by with min query:

See Group by min example online

In the above graphic, one query shows the whole data in the table. While other is showing the group by and MIN function to return the minimum salary paid to each employee.

SQL count with group by example

This query will return the total number of times the salary is paid to each employee by using SQL count aggregate function with the group by clause.

The SQL count with the group by query:

See Group by count example online

As you can see in the above graphic, two queries are shown. One with a simple select statement to show what table contains. The other query uses the group by clause to show the total number of times salary is paid to each employee by using the Count function.