Statistics of classified data using Rollup (i.)

In general, we often use the function of classifying data statistics in writing Web applications. In an E-commerce site, we tend to sell the sales of each type of goods, the number of sales to be classified statistics. So, in the ASP.net, how do we use the DataGrid, on the one hand, display the data in the database, on the one hand, according to the category of data classification and statistics? There are a number of ways to do this, but here's an easy way to forget about using the roll up statement in MS SQL Server.

Let's begin by introducing the roll up statement in MS SQL Server. The Roll up statement is useful when you want to sum up the statistics and aggregate them. The Roll up statement must be used with group by, for example, in the Northwind database, in order to return the total price of the product in the same directory, and the inventory, you can use the following SQL statement:

SELECT
　　　CategoryName,　
　　　SUM(UnitPrice) as UnitPrice,　
　　　SUM(UnitsinStock) as UnitsinStock　
FROM Products
　　INNER JOIN Categories On
　 Products.CategoryID = Categories.CategoryID
GROUP BY CategoryName

The results returned are as follows

And if you want to list all the products in the classification, and can classify the products of each category, the total price statistics, then roll up will be useful. The effect we want to achieve can be shown in the following figure:

Please note the yellow part of the table above, for example,

Represents the total price and quantity of the product in this meat/poultry category, which achieves the purpose of sorting statistics, and the last line

Represents the total price and total quantity of the product in all classifications. It can be seen that the essence of roll up is by column, not only the sum of the categories, but also all the categories, but note that roll up in the classification statistics, in some fields, such as the insertion of NULL values, for example, in the

, rollup in the classification of statistics, first of all will appear in the form of the following

So how do you replace these null values, and you can use the following statement:

SELECT
　CASE
　　WHEN (Grouping(CategoryName)=1) THEN 'MainTotal'
　　ELSE CategoryName
　END AS CategoryName,
　CASE
　　WHEN (Grouping(ProductName)=1) THEN 'SubTotal'
　　ELSE Productname
　END AS ProductName,　
　Sum(UnitPrice) as UnitPrice,　
　Sum(UnitsinStock) as UnitsInStock　
FROM Products
　INNER JOIN Categories On
　　　 Products.CategoryID = Categories.CategoryID
GROUP BY CategoryName, ProductName WITH ROLLUP

Where the case is used. Else statement, replace with "subtotal" when a null value is encountered in a column of ProductName, and "maintotal" value when a null value is encountered in the CategoryName column.