Use group by to group rows [SQL Server]

The group by clause is used to group the result set and summarize and calculate each group of data.

Syntax format:

Group by [column name] [having condition expression]

Group by groups the columns specified by the "column name", groups the records with the same column values, and summarizes and calculates each group. Each group generates a record. If a "having condition expression" exists, the generated group is filtered.

 

Suppose,TablexTable includes:

--------------------------------

Columna columnb columnc

1 ABC 5

1 def 4

1 Ghi 9

2 jkl 8

2 MnO 3

IfColumnAIs a composite column, the result set will eventually have two rows, one of which summarizes the information with a value of 1, and the other of which summarizes the information with a value of 2.

IfColumnAYescomposite column, to referenceColumnBOrColumnC, The two columns must beColumnAReturns the parameters in the aggregate function of a single value. The selection list can contain (Columnb), Sum (Columnc) Or AVG (Columnc:

 SELECT ColumnA,  
           MAX(ColumnB)  AS  MaxB, 
           SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA

This selection statement returns two rowsColumnAEach unique value in returns one row:

ColumnA     MaxB     SumC        

----------- ---- ----------- 

1                ghi           18          

2                mno          11          

(2 row(s) affected)

However, the selection list cannot only containColumnBExpression:

 SELECT  
         ColumnA,   
         ColumnB,       
         SUM(ColumnC) AS SumC
FROM TableX
GROUP BY ColumnA

Because group by can only return one rowColumnAThe value in is 1, so it cannot be returnedColumnAValue 1 inColumnBThe three values (ABC, def, and GHI) in ).

CannotNtext,Text,ImageOrBitColumns use group by or having unless the values returned by their functions belong to other data types. Such functions include substring and cast.