{"id":174,"date":"2016-03-25T07:57:36","date_gmt":"2016-03-25T07:57:36","guid":{"rendered":"https:\/\/sqltutorial.org\/?page_id=174"},"modified":"2025-02-07T21:14:50","modified_gmt":"2025-02-08T04:14:50","slug":"sql-group-by","status":"publish","type":"page","link":"https:\/\/www.sqltutorial.org\/sql-group-by\/","title":{"rendered":"SQL GROUP BY"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the SQL GROUP BY<\/code> clause to group rows based on one or more columns.<\/p>\n\n\n\n

Introduction to SQL GROUP BY clause #<\/a><\/h2>\n\n\n\n

The GROUP BY<\/code> is an optional clause of the SELECT<\/a><\/code> statement. The GROUP BY<\/code> clause allows you to group rows based on values of one or more columns. It returns one row for each group.<\/p>\n\n\n\n

The following shows the basic syntax of the GROUP BY<\/code> clause:<\/p>\n\n\n

SELECT<\/span>\n  column1,\n  column2,\n  aggregate_function (column3)\nFROM<\/span>\n  table_name\nGROUP<\/span> BY<\/span>\n  column1,\n  column2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture illustrates shows how the GROUP BY<\/code> clause works:<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
The table on the left side has two columns id<\/code> and fruit<\/code>. When you apply the GROUP BY<\/code> clause to the fruit<\/code> column, it returns the result set that includes unique values from the fruit<\/code> column:<\/p>\n\n\n
SELECT\n  fruit\nFROM\n  sample_table\nGROUP BY\n  fruit;<\/code><\/span><\/pre>\n\n\n
In practice, you often use the GROUP BY<\/code> clause with an aggregate function<\/a> such as MIN<\/a>, MAX<\/a>, AVG<\/a>, SUM<\/a>, or COUNT<\/a> to calculate a measure that provides the information for each group. <\/p>\n\n\n\n
For example, the following illustrates how the GROUP BY<\/code> clause works with the COUNT<\/code> aggregate function:   <\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
In this example, we group the rows by the values of the fruit<\/code> column and apply the COUNT<\/code> function to the id<\/code> column. The result set includes the unique values of the fruit columns and the number of the corresponding rows.<\/p>\n\n\n
SELECT\n  fruit,\n  COUNT(id)\nFROM\n  sample_table\nGROUP BY\n  fruit;<\/code><\/span><\/pre>\n\n\n
The columns that appear in the GROUP BY<\/code> clause are called grouping columns<\/em>. If a grouping column contains NULL<\/a> values, all NULL values are summarized into a single group because the GROUP BY<\/code> clause considers all NULL values equal.<\/p>\n\n\n\n
SQL GROUP BY examples #<\/a><\/h2>\n\n\n\n
We will use the employees<\/code> and departments<\/code> tables in the sample database<\/a> to demonstrate how the GROUP BY<\/code> clause works.<\/p>\n\n\n\n
\"emp_dept_tables\"<\/figure>\n\n\n\n
The following example uses the GROUP BY<\/code> clause to group the values in department_id<\/code> column of the employees<\/code> table:<\/p>\n\n\n
SELECT<\/span>\n  department_id\nFROM<\/span>\n  employees\nGROUP<\/span> BY<\/span>\n  department_id\nORDER<\/span> BY<\/span>\n  department_id;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Output:<\/p>\n\n\n
 department_id\n---------------\n             1\n             2\n             3\n             4\n             5\n             6\n             7\n             8\n             9\n            10\n            11<\/code><\/span><\/pre>\n\n\n
In this example: <\/p>\n\n\n\n
Try it<\/a><\/p>\n\n\n\n