根据同一字段中的值计算百分比

Question

我使用的是SQL Server。我有一个很好的汇总表，就像你在下面看到的那样。我想为每个熟练程度级别填充(创建) Pct字段。

| MeasurementScale | Grade | ProficiencyLevel | PL_Count | Pct |
|------------------|-------|------------------|----------|-----|
| Mathematics      |   6   | Did Not Meet     |    40    |     |
| Mathematics      |   6   | Approaches       |    86    |     |
| Mathematics      |   6   | Meets            |    83    |     |
| Mathematics      |   6   | Masters          |    42    |     |
| Mathematics      |   6   | Total            |   251    |     |

我基本上想要像下面这样的查询，我只是不知道如何编写它。

SELECT SchoolName
      ,MeasurementScale
      ,Grade
      ,ProficiencyLevel 
      ,PL_Count
      ,(PL_Count / (SELECT PL_Count FROM #PL_Summary1920 WHERE ProficiencyLevel = 'Total')) as Pct
FROM #PL_Summary1920
GROUP BY SchoolName
        ,MeasurementScale
        ,Grade
        ,ProficiencyLevel 
        ,PL_Count

Answer 1

SELECT  V1.*,
        CASE WHEN V2.PL_COUNT = 0 THEN 0
            ELSE V1.PL_Count * 1.0/ V2.PL_COUNT
        END AS  PCT
FROM    (
            SELECT  SchoolName,
                    MeasurementScale,
                    Grade,
                    ProficiencyLevel,
                    SUM(PL_Count)   AS  PL_Count
            FROM #PL_Summary1920 T1
            GROUP BY SchoolName
                    ,MeasurementScale
                    ,Grade
                    ,ProficiencyLevel 
        ) V1
LEFT JOIN   (
                SELECT  SchoolName,
                        MeasurementScale,
                        Grade,
                        SUM(TT.PL_Count)    AS  PL_COUNT
                FROM    #PL_Summary1920 TT
                WHERE   TT.ProficiencyLevel = 'Total'
                GROUP BY SchoolName,
                        MeasurementScale,
                        Grade
            ) V2 ON V2.SchoolName = V1.SchoolName
                    AND V2.MeasurementScale = V1.MeasurementScale
                    AND V2.Grade = V1.Grade

Answer 2

试试这个：

SELECT MeasurementScale
  ,Grade
  ,ProficiencyLevel 
  ,PL_Count
  ,PL_Count * 1.0 / (SELECT PL_Count FROM #PL_Summary1920 WHERE ProficiencyLevel = 'Total') as Pct 
FROM #PL_Summary1920
GROUP BY SchoolName
    ,MeasurementScale
    ,Grade
    ,ProficiencyLevel 
    ,PL_Count

乘以1.0会强制将类型转换为decimal，以便正确显示百分比。它比进行强制转换或转换更干净。