1. There is only one categorized statistical column and only one aggregate is needed. Just add with rollup.

SELECT CASE WHEN GROUPING(GradeName)=1 THEN 'Total' ELSE GradeName END AS grade ,
      SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS Number of boys,
      SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS Number of girls,
      COUNT(Sex) AS Total
FROM dbo.Students
GROUP BY GradeName WITH ROLLUP
ORDER BY GradeName DESC

2. There are several classified summary columns, only one aggregate is needed. After adding rollup, we need to use GROUPING function to judge.

The GROUPING function indicates whether a specified list expression in the GROUP BY list is aggregated. In the result set, if GROUPING returns 1, it indicates aggregation; if GROUPING returns 0, it indicates no aggregation. If GROUP BY is specified, GROUPING can only be used in SELECT < Select > list, HAVING and ORDER BY clauses.

SELECT CASE WHEN GROUPING(GradeName)=1 THEN 'Total' ELSE GradeName END AS grade ,
        ClassName AS class ,
      SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS Number of boys,
      SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS Number of girls,
      COUNT(Sex) AS Total
FROM dbo.Students
GROUP BY GradeName,ClassName WITH ROLLUP
HAVING GROUPING(GradeName)=1 OR GROUPING(ClassName)=0
ORDER BY GradeName DESC

 

SELECT CASE WHEN GROUPING(GradeName)=1 THEN 'Total' ELSE GradeName END AS grade ,
       ClassName AS class ,
       Area AS region ,
      SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS Number of boys,
      SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS Number of girls,
      COUNT(Sex) AS Total
FROM dbo.Students
GROUP BY GradeName,ClassName,Area WITH ROLLUP
HAVING GROUPING(GradeName)=1 OR (GROUPING(ClassName)=0 AND GROUPING(Area) =0)
ORDER BY GradeName DESC

3. There are several classified summary columns, which need to show all the totals and subtotals. No additional judgment is required.

SELECT CASE WHEN GROUPING(GradeName)=1 THEN 'Total' ELSE GradeName END AS grade ,
       CASE WHEN GROUPING(GradeName)=0 AND GROUPING(ClassName)=1 THEN 'Subtotal' ELSE ClassName END AS class ,
      SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS Number of boys,
      SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS Number of girls,
      COUNT(Sex) AS Total
FROM dbo.Students
GROUP BY GradeName,ClassName WITH ROLLUP
ORDER BY GradeName DESC

 

4. There are several classified summary columns, which need to display the total and subtotal of the parts. After adding rollup, you need to add judgment

SELECT CASE WHEN GROUPING(GradeName)=1 THEN 'Total grade' ELSE GradeName END AS grade ,
       CASE WHEN GROUPING(GradeName)=0 AND GROUPING(ClassName)=1 THEN 'Class subtotal' ELSE ClassName END AS class ,
       CASE WHEN GROUPING(ClassName)=0 AND GROUPING(Area)=1 THEN 'Regional subtotal' ELSE Area END AS region ,
      SUM(CASE WHEN Sex=1 THEN 1 ELSE 0 END) AS Number of boys,
      SUM(CASE WHEN Sex=0 THEN 1 ELSE 0 END) AS Number of girls,
      COUNT(Sex) AS Total,
      GROUPING(GradeName) AS GradeName_G,
      GROUPING(ClassName) AS ClassName_G,
      GROUPING(Area) AS Area_G
FROM dbo.Students
GROUP BY GradeName,ClassName,Area WITH ROLLUP
HAVING GROUPING(GradeName)=1 OR GROUPING(Area)=0 OR GROUPING(ClassName)=0
ORDER BY GradeName DESC