tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 35

Ở đây, các quý thứ tư là bao gồm trong các kết quả mặc dù thiếu các dữ liệu của quý IV năm 2009. Các GROUP BY ALL bao gồm các quý thứ tư vì có dữ liệu cho các quý thứ tư năm 2008: | Part II Manipulating Data With Select Here the fourth quarter is included in the result despite the lack of data for the fourth quarter for 2009. The GROUP BY ALL includes the fourth quarter because there is data for the fourth quarter for 2008 SELECT DATEPART qq SalesDate AS Quarter Count as Count Sum Amount as Sum Avg Amount as Avg FROM RawData WHERE Year SalesDate 2009 GROUP BY ALL DATEPART qq SalesDate Result Quarter Count Sum Avg 1 6 218 36 2 6 369 61 3 7 217 72 4 0 NULL NULL The real problem with the GROUP BY ALL solution is that it s dependent on data being present in the table but outside the current Where clause filter. If the fourth quarter data didn t exist for another year other than 2009 then the query would have not listed the fourth quarter period. A better solution to listing all data in a GROUP BY is to left outer join with a known set of complete data. In the following case the VALUELIST subquery sets up a list of quarters. The LEFT OUTER JOIN includes all the rows from the VALUELIST subquery and matches up any rows with values from the aggregate query SELECT Agg. Count Agg. Sum Agg. Avg FROM VALUES 1 2 3 4 AS ValueList Quarter LEFT JOIN SELECT DATEPART qq SalesDate AS Quarter COUNT AS Count SUM Amount AS Sum AVG Amount AS Avg FROM RawData WHERE YEAR SalesDate 2009 GROUP BY DATEPART qq SalesDate Agg ON ORDER BY 302 Aggregating Data 12 Result Quarter Count Sum Avg 1 6 218 36 2 6 369 61 3 7 217 72 4 0 NULL NULL In my testing the fixed values list solution is slightly faster than the deprecated GROUP BY ALL solution. Nesting aggregations Aggregated data is often useful and it can be even more useful to perform secondary aggregations on aggregated data. For example an aggregate query can easily SUM each category and year quarter within a subquery but which category has the max value for each year quarter An obvious MAX SUM doesn t work because there s not enough .