Group by all

Group by all. The difference between "all" and "no" is that after "all" is added, all groups and result sets are included, and even those groups and result sets whose rows do not meet the search conditions specified by the WHERE clause. Note that if all is specified, a null value is returned for the summary column in the group that does not meet the search conditions. Declare @ t table (ID int, Col char (2) insert @ t select 1, 'A' insert @ t select 1, 'A' insert @ t select 2, 'A' insert @ t select 3, 'A' insert @ t select 3, 'A' insert @ t select 4, 'A' insert @ t select 5, 'A' insert @ t select 5, 'A' insert @ t select 5, 'A' -- 1 select ID, count (1) From @ tgroup by id -- 2 select ID, count (1) From @ twhere id <3 group by ID: /* ID ----------- 1 22 13 24 15 3 (5 rows affected) ID- ---------- ----------- 1 22 1 (2 rows affected) */what if we want to get the following results? /* ID ----------- 1 22 13 04 05 0 */-- obviously, you will know what I mean at first glance. Some people will definitely join the union or subquery, have you ever thought about how to make it simple? Look at the syntax below: -- 3 select ID, count (1) from @ twhere id <3 group by all Id results you will have the answer after you run it yourself. This function will be deleted in later versions of Microsoft SQL Server. Avoid using this function in new development work. If you are using this function in future projects, you should modify the applications that are currently using this function. Note the following three points: 1. the cube or rollup operator cannot be used with all. 2. If there is a where clause in the query to access the remote table, the query does not support group by all. 3. For columns with the filestream attribute, group by all is not supported.

Group by all