Today talk about Group by this thing

Group BY this keyword, this statement is too ordinary ~ basically as long as there is a report of the place, there will be its figure.

The general usage is

INSERT  into#TypeValue (TypeID, Col2)VALUES(1N'Name 1'),(1N'Name 2'),(2N'Name 3'),(2N'Name 3'),(3N'Name 4'),(3N'Name 5'),(4N'Name 6'),(4N'Name 6'),(5N'Name 7')SELECTTypeid,col2,COUNT(*) asQty from#TypeValueGROUP  byTypeid,col2typeid Col2 Qty----------- -------------------------------------------------- -----------1Name 111Name 212Name 323Name 413Name 514Name 625Name 71

One grammatical principle is that unless you use an aggregated keyword or constant (such as count, Sum, AVG, and so on), the other fields that appear must appear after group by, and cannot be used as aliases, such as simply changing the sentence above, which only puts the entire case In group by, not directly group by DisplayName. That's the truth.

SELECTTypeID, Case  whenCol2inch('Name 1','Name 2') Then 1                    whenCol2inch('Name 3','Name 4') Then 2                    whenCol2inch('Name 5','Name 6') Then 3                   ELSE 4 END  asDisplayName,COUNT(*) asQty from#TypeValueGROUP  byTypeID, Case  whenCol2inch('Name 1','Name 2') Then 1                    whenCol2inch('Name 3','Name 4') Then 2                    whenCol2inch('Name 5','Name 6') Then 3                   ELSE 4 END

There is also a non-canonical usage Group by all this usage, in the future version should be abolished, but before the abolition of the ~ is still available (but generally not recommended), I also introduce. This all scenario is simple, which is when querying aggregations. A portion of the data is filtered in the Where condition. But there is no effect on the number of rows that are aggregated, and if you look at an instance you will understand

SELECTTypeid,col2,COUNT(*) asQty from#TypeValueWHERETypeID< 4        GROUP  byTypeid,col2typeid Col2 Qty----------- -------------------------------------------------- -----------1Name 111Name 212Name 323Name 413Name 51SELECTTypeid,col2,COUNT(*) asQty from#TypeValueWHERETypeID< 4        GROUP  by  AllTypeid,col2typeid Col2 Qty----------- -------------------------------------------------- -----------1Name 111Name 212Name 323Name 413Name 514Name 605Name 70

The first statement in the query out of the results, bytes put typeid >=4, directly do not return. And the second statement, TypeID >= 4, also leaves a frame in. This is the effect of adding the all keyword.

There are also some possible uses. For example, Cube. The effect is to generate an aggregate row from right to left for each item in group by. A simple example is to change the statement of our example. Some of the null lines, right! Is the aggregation of the ~ finally there is a total aggregation ╮ (╯_╰) ╭

SELECT Typeid,col2,COUNT(* as Qty    from  #TypeValue          GROUP by ROLLUP (typeid,col2)

TypeID Col2 Qty----------- -------------------------------------------------- -----------1Name 111Name 211           NULL                                               22Name 322           NULL                                               23Name 413Name 513           NULL                                               24Name 624           NULL                                               25Name 715           NULL                                               1NULL        NULL                                               9

Of course, some partners said, but I just need you to aggregate the total is OK! Other results set my concubine does not need Ah! Ok ah, change on the line AH ~ need is to rollup the back of the subset with () wrapped up, because this is the judgment set of ~so ~ on the line, there is a, when using rollup, there will be a grouping row number flag is rollup generated aggregation line ~ If you need to assign a value to an aggregated row ~ Note the conversion of the data type Oh ~

SELECT   Case  when GROUPING(TypeID)= 1  Then 'Total' ELSE RTRIM(TypeID)END  asTypeID, Col2,COUNT(*) asQty from#TypeValueGROUP  byROLLUP ((typeid,col2)) TypeID Col2 Qty------------ -------------------------------------------------- -----------1Name 111Name 212Name 323Name 413Name 514Name 625Name 71TotalNULL                                               9

Good ~ today said here ~ I wish you all the Lantern Festival family Health

Today talk about Group by this thing