Complete SQL Server Data Summary Parsing

Preface:

On the forum, I often see people asking "how to achieve data classification and summarization". Many people introduce one or more controls, but do not use relational database language (SQL) to consider the implementation method. Here, I will use an example to illustrate how to use the powerful functions of SQL to classify and summarize data.

Question:Existing table A has the following content: Code repository quantity 01 A 6 01 B 7 02 a 8 02 B 9 Now you want to query this format by encoding: -------------------- 01 A 6 01 B 7 Summary Subtotal: 13 02 a 8 02 B 9 Summary Subtotal: 17 Q: How can this problem be achieved? At first glance, it seems very easy to use group? However, if you study it carefully, you will feel that group by is powerless and you will have nothing to do. So what should we do? Don't worry, SQL Server has already helped us. Next, come with me. First, let's take a look: The rollup operator is useful when generating reports that contain subtotal and aggregate. The result set generated by the rollup operator is similar to the result set generated by the cube operator. ====================================== The result set generated by the cube operator is a multi-dimensional dataset. A multi-dimensional dataset is an extension of fact data. Fact data is the data that records individual events. The extension is created on the columns that the user intends to analyze. These columns are called dimensions. A multi-dimensional dataset is a result set that contains all possible combinations of cross tables for each dimension. The cube operator is specified in the group by clause of the SELECT statement. The statement selection list should contain dimension columns and aggregate function expressions. The dimension column and keyword with cube should be specified for group. The result set contains all possible combinations of values in the dimension column, and the aggregated values in the basic rows that match the combination of these dimension values. ====================================== The difference between cube and rollup is: The result set generated by cube displays the aggregation of all the combinations of the values in the selected column. The result set generated by rollup displays the aggregation of a certain hierarchy of values in the selected column. After reading the above passage, what have you realized? If not, then ...... Hey, your understanding is not enough. It's still early to go to the "Three flowers" Summit :). Next let's take a look (note that the answer will be announced soon ): Select Code, repository, Sum (Quantity) As Quantity From A Group   By Code, repository With Rollup -- The key is with rollup. -- Of course, you can also use with cube, but the results will be a little different After reading the above section, you may still find yourself confused. It doesn't matter if you don't understand it. Do it yourself. First, create a table a mentioned above and input several rows of data; Next, open your SQL Server Query analyzer, connect the server that contains the table a you created above, and select the database that contains the table; Then: copy the preceding SQL statement, paste to the query analyzer, press F5, how? What is shown below? Is it the same as below? Code repository quantity 01 A 6 01 B 7 01 Null 13 02 A 8 02 B 9 02 Null 17 Null Null 30 -- If you use with cube, there will be two more rows after the result set (if you only enter several rows of data in the example ): Null A 14 Null B 16 Success! How can there be so many "null" values in the results? Ha, don't worry. These rows are exactly the data rows we want to summarize. It's not hard to see: 01 null 13 is a summary of the number of all warehouses encoded as 01; 02 null 17 is a summary of the number of all warehouses encoded as 02; Null null 30 is a summary of the number of data rows. How? What is the answer? Is it easy? Of course, there are still some shortcomings above, that is, there are many "null. How can we remove meaningless null values? Next we will optimize it. 1. Replace null with grouping Select   Case   When ( Grouping (Encoding) =   1 ) Then   ' All ' Else   Isnull (Encoding, ' Unknown ' ) End   As Encoding, Case   When ( Grouping (Warehouse) =   1 ) Then   ' All ' Else   Isnull (Warehouse, ' Unknown ' ) End   As Warehouse, Sum (Quantity) As Quantity From A Group   By Code, repository With Rollup -- Use Case functions as appropriate I will not write it here, that is, replace all the above "null" values with the "all" string.   2. ExploitationProgramFurther Optimization // Generally, we must optimize the results generated by the preceding SQL statement to display the requirements. The following describes the natural language: While (last record not reached ){ If the encoding value is not all and the warehouse value is all { Replace the encoded value with "Subtotal:" and use the warehouse Value "" Replace; Mark the color of this line as gray; } The else encoded value is all, and the warehouse value is also all. { Replace the encoding value with "Total:" and use the warehouse Value "" Replace; Color the line as light green; } Pointer to the next one; } // Of course, you can make full use of your imagination to dress up the form beautifully, so that I will not be arrogant.

Conclusion: As mentioned above, I don't know if you understand it. It is limited to the author's ability to express words. Please forgive me for not explaining clearly.