SQL with cube

Write down the usage of with cube in idle time
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 will contain All Possible combinations, and aggregate values in the basic row that match the combination of these dimension values.
See the following table first:


We use ID aggregation to query the average score.

The only difference between this SQL statement and the preceding statement is that it is followed by the with cube, but the result is more than the previous one. It is also the average score based on the class ID, the difference is that the last result is null 4. What is this? Let's look at the first section. With cube contains all possible combinations. Based on the ID, it helps us find the average sum by ID, and then calculate all the average scores, that is, the average scores of the table IDs are 1 and 2. This can be applied in many places, such as wage statistics by gender and Performance Statistics by department ......
In fact, we often get more accurate data, that is ~ The above result should be in decimal form ~ In fact, the above results are not accurate, and SQL rounding it down ~
Our formal approach ~

We found that there were decimal places, but after careful reading, the result was incorrect. In this way, the SQL statement converts the result of the previous step into decimal places, if two zeros are added directly to the end of Step 4, the error is displayed. Why is the SQL statement like this? How can we solve this problem? In fact, in the votedetails table, our votenum field is int type, when we calculate With AVG (votenum), SQL will calculate with the votenum data type, that is, Int, so the operation result is int type, it will be rounded up, although we Cast ( AVG (Votenum) As Decimal (), But only converts the integer calculated by AVG to decimail () to retain two decimal places.
Knowing this principle makes it easy! Convert the data type before AVG calculation. Try the following:

It doesn't matter if the number of decimal places is the expected result. We can convert the result after AVG calculation and keep the decimal places!