MS SQL Server - GROUP BY

Video is in Swedish

Mastering MS SQL Server’s GROUP BY Clause  

In Microsoft SQL Server, the GROUP BY clause is a powerful tool used to group rows of data based on one or more columns. This clause is commonly used in conjunction with aggregate functions such as SUM, AVG, MAX, and MIN to perform calculations on grouped data.

Basic Syntax  

The basic syntax of the GROUP BY clause is as follows:

SELECT column1, column2, ...
FROM table_name
GROUP BY column1, column2, ...;

In this syntax, column1, column2, etc. are the columns that you want to group your data by. The table_name specifies the name of the table from which you want to retrieve the data.

Example 1: Grouping by a Single Column  

Suppose we have a table called employees with columns employee_id, name, and salary. We can use the GROUP BY clause to group the employees by their salary as follows:

SELECT salary, AVG(salary) AS average_salary
FROM employees
GROUP BY salary;

This query will return a result set that shows the average salary for each unique salary value in the employees table.

Example 2: Grouping by Multiple Columns  

We can also use the GROUP BY clause to group data by multiple columns. For example, suppose we want to group our employees by their department and job title as follows:

SELECT department, job_title, COUNT(*) AS num_employees
FROM employees
GROUP BY department, job_title;

This query will return a result set that shows the number of employees in each department and job title combination.

Example 3: Using Aggregate Functions with GROUP BY  

We can use aggregate functions such as SUM, AVG, MAX, and MIN with the GROUP BY clause to perform calculations on grouped data. For example, suppose we want to calculate the total salary for each department as follows:

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

This query will return a result set that shows the total salary for each department in the employees table.

Conclusion  

In conclusion, the GROUP BY clause is a powerful tool in MS SQL Server that allows you to group rows of data based on one or more columns. By combining it with aggregate functions, you can perform complex calculations and analysis on your data. With practice and experimentation, you’ll become proficient in using the GROUP BY clause to extract valuable insights from your data.

Video  

Sourcecode