The GROUP BY clause in SQL
The GROUP BY is a clause in SQL that is used with the SELECT statement for grouping the rows. The purpose is generally performing the aggregate operation like SUM, COUNT, AVG etc. to the group data.
A few important points about GROUP BY clause:
- The SQL GROUP BY is used with the select statement
- If WHERE clause is used in the SELECT statement then GROUP BY is used after it.
- The GROUP BY clause is used before the ORDER BY clause
- The GROUP BY clause is often used with the aggregate functions.
- The column(s) used for grouping data must appear in the SELECT statement.
- You may use one or more columns in the GROUP BY clause for grouping data.
A simple example of GROUP BY clause
For the GROUP BY examples, I will use the sto_emp_salary_paid table that stores the employee’s salaries paid every month. You can see the demo table with data in the graphic below.
The first query simply displays the complete table data and the second query uses the GROUP BY SQL query for getting the sum of salary paid to each employee:
Query:
|
1 2 3 4 5 |
SELECT emp_name, SUM(emp_sal_paid) As "Total Paid" FROM sto_emp_salary_paid GROUP BY emp_name |
Using Where clause with GROUP BY query
In this example, I set the criteria by using the where clause in above SELECT statement. So, we will get the SUM of salaries for the employees who got paid 3500+. The records with less than this value will not be included in the SUM function. See the query and table output and you may compare the result with above output:
SQL query:
|
1 2 3 4 5 6 7 |
SELECT emp_name, SUM(emp_sal_paid) As "Total Paid" FROM sto_emp_salary_paid WHERE emp_sal_paid > 3500 GROUP BY emp_name |
As mentioned earlier, the GROUP BY clause comes after the Where clause as shown in above example.
Note: You may execute these example queries in MS SQL server and MySQL server as well. Just adjust a little bit.
Using HAVING clause in GROUP BY
Let us say we require the records of those employees whom total paid amount is greater than or equal to 5000.
For that, we may calculate the SUM of each employee and then use a criterion in HAVING clause.
Query:
|
1 2 3 4 5 6 7 |
SELECT emp_name, SUM(emp_sal_paid) As "Total Paid" FROM sto_emp_salary_paid GROUP BY emp_name HAVING SUM(emp_sal_paid) >= 5000 |
The example of using ORDER BY clause with GROUP BY
The ORDER BY clause is used to sort the results in ascending or descending order. You may also sort the results fetched after using the SELECT with GROUP BY clause.
The ORDER BY clause is used after the GROUP BY as shown in the query below:
The query:
|
1 2 3 4 5 6 7 |
SELECT SUM(emp_sal_paid) As "Total Salary", emp_name FROM sto_emp_salary_paid GROUP BY emp_name ORDER BY emp_name; |
The example of using COUNT and GROUP BY clause
The above examples show using the SUM function with GROUP BY clause. As mentioned earlier, you may use any aggregate function with GROUP BY including the COUNT function.
The following query shows using the COUNT for getting the number of salaries paid to each employee in our example table. Also, I used the ORDER BY clause with DESC keyword in the same query:
Query:
|
1 2 3 4 5 6 7 |
SELECT COUNT(*) As "No of Salaries", emp_name FROM sto_emp_salary_paid GROUP BY emp_name ORDER BY emp_name DESC; |
You may learn more about the SQL COUNT function in its tutorial.
Using MAX function with GROUP BY example
Let us extend the above example scenario while using the MAX function with GROUP BY clause. The query will return the total number of salaries for each employee along with maximum amount paid.
The MAX function is used in the same query; so you may use multiple aggregate functions as well. Also, for demo only, I used ORDER BY clause with two aggregate columns; one with default ASC order and the other with DESC order:
|
1 2 3 4 5 6 7 |
SELECT MAX(emp_sal_paid) As "Maximum Salary",COUNT(*) As "Total Salaries", emp_name FROM sto_emp_salary_paid GROUP BY emp_name ORDER BY MAX(emp_sal_paid), COUNT(*) DESC; |
The resultant record-set:
Using MIN function example
Just a little variation in the above query and this example shows using the MIN function with GROUP BY clause:
The Query:
|
1 2 3 4 5 6 7 |
SELECT MIN(emp_sal_paid) As "Minimum Salary",COUNT(*) As "Total Salaries", emp_name FROM sto_emp_salary_paid GROUP BY emp_name ORDER BY MIN(emp_sal_paid) DESC, COUNT(*) ASC; |
The example of using two columns in GROUP BY
For explaining more than one column usage in the GROUP BY clause, I am going to use the Sales table. The sto_sales table stores the sales based on countries and regions (for the demo only).
In the SELECT – GROUP BY statement, we will get the sum of sales on the basis of:
- Country
- Regions
The query should sum the record where it finds the same Country and Regions, unlike the above examples where data were grouped on the basis of the single column. Have a look:
The two columns query:
|
1 2 3 4 5 |
SELECT Country_of_Sale, Region_of_Sale,SUM(No_of_Sales) FROM sto_sales GROUP BY Country_of_Sale,Region_of_Sale; |
The output:
The example of using GROUP BY ROLLUP
The following example shows using the GROUP BY ROLLUP where two columns are specified. By looking at the complete data and records returned by GROUP BY ROLLUP, you will understand how it works:
The Query:
|
1 2 3 4 5 |
SELECT Country_of_Sale, Region_of_Sale,SUM(No_of_Sales) FROM sto_sales GROUP BY ROLLUP (Country_of_Sale,Region_of_Sale); |
Using GROUP BY CUBE example
The GROUP BY CUBE is used to create groups of all possible combination of specified columns. This is what official statement says:
“For GROUP BY CUBE (a, b) the results has groups for unique values of (a, b), (NULL, b), (a, NULL), and (NULL, NULL).”
Have a look at the result-set returned after using it in our demo sales table:
Query:
|
1 2 3 4 5 |
SELECT Country_of_Sale, Region_of_Sale,SUM(No_of_Sales) As "Sales Total" FROM sto_sales GROUP BY CUBE (Country_of_Sale,Region_of_Sale); |
The output:
