SQL Server GROUP BY operator

Preamble

Using the GROUP BY operator, the SELECT operator in SQL Server can get information from multiple records and group the 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. The prerequisites for choosing records are as follows.
• ORDER BY – It’s optional.  The expression used to order the records in the result set; multiple expressions must be specified, with commas separating the values.
• 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 examine how to employ SQL’s GROUP BY with SUM function.
In this illustration, we have a table with the information below:

Put the following SQL query here:

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

There will be two records chosen. These are the outcomes you’ll get:

In this example, all of the salaries for each department were added using the SUM function, and the resulting value was given the moniker “total_sal.” Since dept is not contained within the SUM function, it needs to be specified in the GROUP BY clause.

Using GROUP BY with function COUNT

Let’s examine how to use SQL’s GROUP BY option with the COUNT function.

In this illustration, the product table contains the information below:

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;

We’ll pick three records. The outcomes that you should see are as follows:

In this example, the number of total_prod for each cat_id was determined using the COUNT function, and the result was specified as the alias “total_prod.” In the WHERE clause, we filtered out and excluded all cat_id values that have a value of NULL. Cat_id must be specified in the GROUP BY clause because it is not included in the COUNT function.

Using GROUP BY with function MIN

Let’s now examine how to use the SQL MIN function in conjunction with the GROUP BY option.

We’ll use the table with the following information once more in this illustration:

Enter the following SQL statement:

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

There will be two records chosen. You will see the following results:

In this example, we used the MIN function to return the lowest pay for each department, and we gave the resulting values to the MIN function’s “low_sal” alias. Dept must be specified in the GROUP BY clause because it is not contained in the MIN function.

Using GROUP BY with the MAX function

Let’s now examine how to combine the MAX feature with the GROUP BY offer.

Reusing the employees table, let’s determine the highest pay for each dept_id this time:

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, the MAX function was used to return the highest possible salary value for each department, and the output was given the alias “high_salary.” Since the dept column is not included in the MAX function, it must be specified in the GROUP BY clause.

More Examples:

All examples will be on this table unless otherwise stated:

Example №1
The records in this example are divided into three age groups: 23 years, 24 years, and 25 years. The sum function is then used for each group, totaling the wages for that group.

In order to determine the total salaries for each of the groups (23, 24 and 25 years), the following formula will be used:

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 illustration, we use the additional condition that we should not include all table entries:

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
The records in this example are divided into three age groups: 23 years, 24 years, and 25 years. The function max is then used for each group to determine the highest pay within that 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
We can now use the min function to determine the minimum wage for 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 following lines will be chosen by the SQL query:

Understanding the use of GROUP BY clause in SQL

About Enteros

​​​​Enteros offers a patented database performance management SaaS platform. It finds the root causes of complex database scalability and performance problems that affect business across a growing number of cloud, RDBMS, NoSQL, and machine learning database platforms.