How to use Group by

SQL if you want to group the query, generally use the group by statement, how to skillfully use the group by statement, I divided the following points to summarize.

1. Group by and aggregate functions
2. Group by and having
3. Places to be aware of

Group by and aggregate functions

Group by is generally used in conjunction with aggregate functions. This is because the result set becomes multiple groupings after group by, and each grouping may contain multiple records, and to operate on each grouping, you must use aggregate functions that can work on multiple records. For example, the following example uses the group by and aggregate functions to query the total number of records for each PNR, and the SQL statement is as follows.

1 -- 1,group by and aggregation functions 2 -- Query the total number of records per PNR 3 SELECT NEWPNR,COUNT(* from dbo. Remotesession4GROUP by NEWPNR;

What if you want to filter the grouping? How to do it, please keep looking down.

Group by and having

To filter the results after grouping, it is generally done with a having statement. Take a look at the following example, the code below.

 1  --  2  --  >1 (or duplicate) PNR  3  select  newpnr,count  (* ) from   dbo. Remotesession  4  group  by   NEWPNR  5  having  count  (* ) >  1 ; 

Places to be aware of

There are several areas of particular concern regarding the use of group by.

1. The field specified by the SELECT statement is either contained in a GROUP by statement as a grouping, or it is included in an aggregate function (e.g., count,sum, etc.).
2. Notice the difference between where and having, where the result set is filtered before grouping, and having is filtering the result set after the group by group.

Resources

1. Use of GROUP by in SQL
2. The use of SQL Group by and having
3. The group by and having of the SQL tutorial

How to use Group by