SQL Server GROUP BY operator

SQL Server GROUP BY operator can be used in SELECT operator to collect data by several records and group results by one or more columns.

Syntax of GROUP BY statement in SQL

SELECT expr1, expr2, … expr_n,
  aggregate_function (aggregate_expr)
  FROM tab
  [WHERE conds]
  GROUP BY expr1, expr2, … expr_n
  [ORDER BY expr [ ASC | DESC ]];

where:

• expr1,2, …_n – Expressions that are not encapsulated in the aggregate function and must be included in GROUP BY at the end of the SQL query.
• aggr_function – This is an aggregate function such as SUM, COUNT, MIN, MAX or AVG.
• aggr_expression – This is the column or expression for which aggregate_function will be used.
• tab – The tables from which you want the records. The FROM sentence must contain at least one table.
• WHERE conds – It’s optional. These are the conditions that must be met to select records.
• ORDER BY – It’s optional. The expression used to sort the records in the result set. If more than one expression is specified, the values must be separated by commas.
• ASC – It’s optional. ASC sorts the result set in ascending order by. This is the default behavior if no modifier is specified.
• DESC – It’s optional. DESC sorts the result set in descending order.

Using GROUP BY with SUM function

Let’s see how to use GROUP BY with SUM function in SQL.
In this example, we have a table with the following data:

Enter the following SQL statement:

SELECT dept,
        SUM(salary) AS total_sal
    FROM employees
 GROUP BY dept;

Two records will be selected. Here are the results that you will get:

In this example, we used the SUM function to add all the salaries for each dept, and we gave the nickname “total_sal” to the results of SUM(sal). Since dept is not encapsulated in the SUM function, it must be specified in the GROUP BY sentence.

Using GROUP BY with function COUNT

Let’s see how to use the GROUP BY offer with the COUNT function in SQL.

In this example, we have a product table with the following data:

Enter the following SQL statement:

SELECT cat_id,
      COUNT(*) AS total_prod
   FROM prod
 WHERE cat_id IS NOT NULL
 GROUP BY cat_id
 ORDER BY cat_id;

Three records will be selected. Here are the results that you should get:

In this example, we used the COUNT function to calculate the number of total_prod for each cat_id, and we specified the alias “total_prod” as the result of the COUNT function. We excluded all cat_id values that have a value of NULL and filtered them out in the WHERE sentence. Since cat_id is not encapsulated in the COUNT function, it must be specified in the GROUP BY sentence.

Using GROUP BY with function MIN

Let’s now look at how to use the GROUP BY offer with the MIN function in SQL.

In this example, we will again use the table with the following data:

Enter the following SQL statement:

SELECT dept,
      MIN(sal) AS lowest_sal
    FROM empl
 GROUP BY dept;

Two records will be selected. Here are the results that you will get:

In this example, we used the MIN function to return the minimum salary for each dept, and we assigned the results to the MIN function <a> alias “low_sal”. Since dept is not encapsulated in the MIN function, it must be specified in the GROUP BY sentence.

Using GROUP BY with the MAX function

Finally, let’s look at how to use the GROUP BY offer with the MAX feature.

Let’s use the employees table again, but this time we will find the highest salary for each dept_id:

Enter the following SQL statement:

SELECT dept,
      MAX(sal) AS highest_sal
   FROM empl
 GROUP BY dept;

Two records will be selected. Here are the results that you should get:

In this example, we used the MAX function to return the maximum salary value for each dept, and we assigned the alias “high_salary” to the result of the MAX function. The dept column must be specified in the GROUP BY clause since it is not encapsulated in the MAX function.

More Examples:

All examples will be on this table unless otherwise stated:

Example №1
In this example the records are grouped by age (there will be 3 groups – 23 years, 24 years and 25 years). Then for each group the sum function is applied, which sums up the wages within that group.

As a result, for each of the groups (23 years, 24 years and 25 years) the total salaries within this group will be calculated:

SELECT f_age, SUM(f_sal) as sum FROM workers_1 GROUP BY f_age

The SQL query will select the following lines:

Example №2
In this example, we use the additional condition where to take not all entries from the table:

SELECT f_age, SUM(f_sal) as sum FROM workers_1 WHERE id>=2 GROUP BY f_age

The SQL query will select the following lines:

Example №3
In this example the records are grouped by age (there will be 3 groups – 23 years, 24 years and 25 years). Then for each group the function max is applied, which finds the maximum salary within this group:

SELECT f_age, MAX(f_sal) as max FROM workers_1 GROUP BY f_age

The SQL query will select the following lines:

Example №4
And now with the help of the min function we will find the minimum wage within this group:

SELECT f_age, MIN(f_sal) as min FROM workers_1 GROUP BY f_age

The SQL query will select the following lines:

Example №5
And now with the help of the count function you will find the number of records in the group:

SELECT f_age, COUNT(*) as count FROM workers_1 GROUP BY f_age

The SQL query will select the following lines:

Understanding the use of GROUP BY clause in SQL