GroupBy in one-to-one SQL (Group by SQL Server)

GroupBy statements are used to group the results of a selection, and GroupBy are usually used with aggregate functions. For example, there is a table below:

If we want to group it by City and calculate the sum of the wages for each city, you can use the following statement:

SELECT [ City],SUM([Salary]) astotalsalary from [Sample].[dbo].[Tblemployee]GROUP  by [ City]

Here is the result of the execution:

One thing to note here is that if we do not write the group by statement, then the above code will error. The reason is simple. Since we want to sum the SUM function, we should specify how to group it.

MSG 8120, Level A, State 1, line 2
Column ' Sample.dbo.tblEmployee.City ' is invalid in the select list because it's not contained in either an aggregate func tion or the GROUP by clause.

Below, we went further. The code above demonstrates grouping the results according to city and finding the sum of the wages for each group of city. Now we want to do this based on gender (Gender), which means that the sum of the wages of people of different sexes in the same city is shown. The following code can implement this functionality:

SELECT [ City],[Gender],SUM([Salary]) astotalsalary from [Sample].[dbo].[Tblemployee]GROUP  by [ City],[Gender]ORDER  by [ City]

Here are the results of the query:

At the very beginning of the article, the Group by statement is usually used with aggregate functions. The previous example demonstrates the use of the group by and sum functions, but we can also add more aggregate functions to the query statement. For example, if we want to calculate the total number of employees for each city based on the first example, you can use the following query statement:

SELECT [ City],[Gender],SUM([Salary]) asTotalsalary,COUNT(*) asTotalemployee from [Sample].[dbo].[Tblemployee]GROUP  by [ City],[Gender]ORDER  by [ City]

Here are the results of the query:

Well, we've learned from a few small examples above that the Group by statement can be used with different aggregation functions. A friend might ask, what if I want to sift through the results? For example, what do we want to do with the statistical results of men (Male) employees who are gender-only? Smart you must have thought of it. Use the WHERE statement. Absolutely right! Using the Where statement to filter the results of the query, consider the following sample code:

SELECT [ City],SUM([Salary]) asTotalsalary,COUNT(*) asTotalemployee from [Sample].[dbo].[Tblemployee]WHERE [Gender] = 'Male'GROUP  by [ City]ORDER  by [ City]

Here are the results of the query:

As you can see, we do what we want with the WHERE statement: only employees with male gender are selected and grouped according to city, and then the total wage and total number of employees are calculated.

In fact, in addition to using the WHERE statement we can also use the HAVING clause to filter the results of the query, see the following code:

SELECT [ City],[Gender],SUM([Salary]) asTotalsalary,COUNT(*) asTotalemployee from [Sample].[dbo].[Tblemployee]GROUP  by [ City],[Gender] having [Gender] = 'Male'ORDER  by [ City]

Here is the result of the operation:

Again, we get the results we want.

So what's the difference between using a WHERE clause and HAVING clause? The following is a summary (which is also a frequently asked question in the interview):

1. The where statement can be applied in a SELECT, Insert, UPDATE statement, but the HAVING clause can only be used in a SELECT statement
2. The Where statement filters the result set before the aggregation function takes effect, however, the HAVING clause is when the aggregation function takes effect before the result set is filtered
3. An aggregate function cannot appear in a WHERE clause, but an aggregate function can appear in the HAVING clause

GroupBy in SQL (Group by SQL Server)