Module 2: Group Data with GROUP BY Clause Part 1

Previous Tutorial - Next Tutorial

SQL can also be used for data analysis to transform data that are already present in the database to some valuable information that helps organizations in making some key decisions for their business. Now we’ll be going to learn about the GROUP by clause, which targets rows having common value into a smaller set of rows.

The GROUP by statement group the rows that have the same value into summary rows. For example, find the total number of employees in the logistic department. GROUP by clause is often used in conjunction with the SQL aggregation function such as Count, Max, Min, Some, etc to aggregate data.

This is the general syntax of the GROUP by clause. In the GROUP by clause, you can specify either a single column or more than one column to group the result, grouping and aggregating data can provide a meaningful context for analyzing business information. It allows users to perform comparisons or find some anomalies in the data.

Typically, we group the data on column phase, which usually describes characteristics of the records, for example, category color or some period such as year or month, etc. These fields are often used for counting records, which is a form of aggregation. If the data is grouped and then aggregated, the result will be a list of distinct group values, along with an aggregated value for each group.

I have highlighted some records from the employee table. Here, you can see that some of the employees have a common job title. For example, three employees have accounts receivable specialist job titles, while another five employees have application specialist job titles. Now, I want to group this record based on the job title column. Let's see how we can use the GROUP by clause to group this record. Now let's open the SQL Server Management Studio to see this clause in action.

Data Group by Clause Here in SQL server management studio, I have written one query, which retrieves the information from the employee table. Now if I execute this query, then I will get this record. Now my requirement is I want to see how many employees have the same job title or share the same job title.</p>

For example, two employees have the same job title accountant, four employees have again the same job title application specialist. I want to list out the job title and the account of the total number of employees who have this same job title. For that, I need to redefine this query, and let's see how we can use the GROUP by clause.

First, let me remove this column and job title, I also want to count the total number of employees who have this common job title. So for that, I need to use SQL aggregate function count.

Now, after the FROM clause, I have to use the GROUP by clause. Now if I execute this query, I will get this result. This query group the rows by job title and count the number of employees for a distinct group.

In this query, we have used the aggregate function count, to count the number of employees who have the same job title. When you just need to know how many records are there in a table or how many records share a common attribute value, you can use the count aggregate function. We will understand all the required aggregate functions in the next slide.

This is just for your information. So, for example, if you want to get the total number of employees from this employee table, then you can easily get it using this count function like(SELECT COUNT(*) FROM HumanResources.Employee), now if I execute this query, then you will get that number of records.

If I remove this aggregate function from this query, the result will simply be a single row containing the aggregate values for all the records. It means you will get a unique record in the result. So for example, now if I execute this query, then you will get the unique job title, a record from this employee table.

Well, in T-SQL, there is another alternative to get unique records. You can get a unique record using a distinct clause. This distinct clause will remove the duplicate records from the results set. So this query can be rewritten as (SELECT DISTINCT jobTitle FROM HumanResources.Employee). When you use this distinct clause at that time, no need to specify this GROUP by clause.

Now if I'm executing these two queries together, then I will get the same number of records. Even performance wise there is no difference between these two clauses. So you can check it by open the execution plan of this query. In SQL Server Management Studio, to enable the execution plan, you need to click on this button, which says include the actual execution plan.

Now, when I enable this button and if I'm executing this query again, then I will get the execution plan of both these queries. So here you can see that both queries consume 50 percent of overall cost and both queries using this distinct sort operator. Now how to study this execution plan is out of scope for this course. You can also use multiple columns to group the results as per the requirement.

Sort Data Group by Clause

This time I want to find out how many employees have the same job title and are hired in the same year. So for that in this query, I need to use one inbuilt function of the SQL server which is YEAR. So YEAR function will return the year from the specified column, which is given as input to this function. So let's see the basic function of YEAR. If we execute this function, you will get the GETDATE and now let us specify the YEAR function.

Let us give the GETDATE function as input and now when I execute this query, I will get the year as the output. So the same way we can use this YEAR function in this query.

Sorting Data Group by Clause

Now, when I execute this query, I will get those employees who share the same job title and same hiring date. For example, three employees have the same job title as accounts specialist and are hired in the same year.

Web API for developers Free Trial Offline SDK

Here's SQL video tutorial:

Other useful tutorials: