選擇時計算百分比-T-SQL

我有一個存盤程序：

ALTER PROCEDURE GetReportData
AS
BEGIN
    SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(LOC.BranchCode) as Total,

        ----------- Not Processed Yet
        -- Count
        (SELECT COUNT(WS.ID)
            FROM DBO.WorkingSession AS WS
            JOIN DBO.Location AS LO ON WS.LocationID = LO.LocationID AND WS.BranchCode = LO.BranchCode
            WHERE WS.Status = 5 OR WS.Status = 6 AND LO.BranchCode = LOC.BranchCode) AS CountNotProcessedYet,
        ------------

        ----------- Processing
        -- Count
        (SELECT COUNT(WS.ID)
            FROM DBO.WorkingSession AS WS
            JOIN DBO.Location AS LO ON WS.LocationID = LO.LocationID AND WS.BranchCode = LO.BranchCode
            WHERE WS.Status = 3 AND LO.BranchName = LOC.BranchName) AS CountProcessing,
        ------------
    FROM DBO.WorkingSession AS SS
    JOIN DBO.Location AS LOC ON SS.LocationID = LOC.LocationID AND SS.BranchCode = LOC.BranchCode
    JOIN DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY LOC.SubCompanyNameVN, LOC.BranchName, LOC.BranchCode
    ORDER BY LOC.SubCompanyNameVN
END

結果：

但我的預期結果是：

所以我像這樣更新我的存盤程序：

ALTER PROCEDURE GetReportData
AS
BEGIN
    SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(LOC.BranchCode) as Total,

        ----------- Not Processed Yet
        -- Count
        (SELECT COUNT(WS.ID)
            FROM DBO.WorkingSession AS WS
            JOIN DBO.Location AS LO ON WS.LocationID = LO.LocationID AND WS.BranchCode = LO.BranchCode
            WHERE WS.Status = 5 OR WS.Status = 6 AND LO.BranchCode = LOC.BranchCode) AS CountNotProcessedYet,
         --- Percent
        ROUND((CountNotProcessedYet/Total)*100,2)
        ------------

        ----------- Processing
        -- Count
        (SELECT COUNT(WS.ID)
            FROM DBO.WorkingSession AS WS
            JOIN DBO.Location AS LO ON WS.LocationID = LO.LocationID AND WS.BranchCode = LO.BranchCode
            WHERE WS.Status = 3 AND LO.BranchName = LOC.BranchName) AS CountProcessing,
        --- Percent
        ROUND((CountProcessing/Total)*100,2)
        ------------
    FROM DBO.WorkingSession AS SS
    JOIN DBO.Location AS LOC ON SS.LocationID = LOC.LocationID AND SS.BranchCode = LOC.BranchCode
    JOIN DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY LOC.SubCompanyNameVN, LOC.BranchName, LOC.BranchCode
    ORDER BY LOC.SubCompanyNameVN
END

錯誤：

有什么方法可以計算百分比嗎？

uj5u.com熱心網友回復：

首先，在您收到的錯誤訊息上，您不能在同一級別上參考列別名。

CountProcessing/Total

您可以使用一個CTE或匯出查詢來計算Total和CountProcessing，然后計算出Percent使用列別名外查詢。

您可以使用CASE運算式而不是子查詢來簡化您的查詢

SELECT
    LOC.SubCompanyNameVN,
    LOC.BranchName,
    COUNT(LOC.BranchCode) as [Total],
    SUM (CASE WHEN SS.Status IN (5,6) THEN 1 ELSE 0 END) as [CountNotProcessedYet],
    SUM (CASE WHEN SS.Status IN (3)   THEN 1 ELSE 0 END) as [CountProcessing],
    ROUND (SUM (CASE WHEN SS.Status IN (3)   THEN 1 ELSE 0 END) * 100.0 / COUNT(LOC.BranchCode), 2) as [Percent]
FROM DBO.WorkingSession AS SS
JOIN DBO.Location AS LOC ON SS.LocationID = LOC.LocationID AND SS.BranchCode = LOC.BranchCode
JOIN DBO.Status AS ST ON SS.Status = ST.ID
GROUP BY LOC.SubCompanyNameVN, LOC.BranchName
ORDER BY LOC.SubCompanyNameVN

uj5u.com熱心網友回復：

您不能在該程序的一個步驟中同時為列命名并為計算列使用相同的名稱。更改您的查詢，如下所示

ALTER PROCEDURE GetReportData
AS
BEGIN
  SELECT
    SubCompanyNameVN,
    BranchName,
    Total,
    CountNotProcessedYet,
    ROUND((CountNotProcessedYet/Total)*100,2),
    CountProcessing,
    ROUND((CountProcessing/Total)*100,2)
  FROM
    (SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(LOC.BranchCode) as Total,

        ----------- Not Processed Yet
        -- Count
        (SELECT COUNT(WS.ID)
            FROM DBO.WorkingSession AS WS
            JOIN DBO.Location AS LO ON WS.LocationID = LO.LocationID AND WS.BranchCode = LO.BranchCode
            WHERE WS.Status = 5 OR WS.Status = 6 AND LO.BranchCode = LOC.BranchCode) AS CountNotProcessedYet,
        ----------- Processing
        -- Count
        (SELECT COUNT(WS.ID)
            FROM DBO.WorkingSession AS WS
            JOIN DBO.Location AS LO ON WS.LocationID = LO.LocationID AND WS.BranchCode = LO.BranchCode
            WHERE WS.Status = 3 AND LO.BranchName = LOC.BranchName) AS CountProcessing,
       
    FROM DBO.WorkingSession AS SS
    JOIN DBO.Location AS LOC ON SS.LocationID = LOC.LocationID AND SS.BranchCode = LOC.BranchCode
    JOIN DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY LOC.SubCompanyNameVN, LOC.BranchName, LOC.BranchCode) T
  ORDER BY LOC.SubCompanyNameVN
END

uj5u.com熱心網友回復：

也許像這樣

    SELECT 
        LOC.SubCompanyNameVN,
        LOC.BranchName,
        COUNT(SS.ID) as Total,
        COUNT(CASE WHEN SS.Status IN (5, 6)
              THEN SS.ID END) AS CountNotProcessedYet, 
        ROUND((COUNT(CASE WHEN SS.Status IN (5, 6)
              THEN SS.ID END)/COUNT(SS.ID))*100,2) AS PercentNotProcessedYet, 
        COUNT(CASE WHEN SS.Status = 3
              THEN SS.ID END) AS CountProcessing, 
        ROUND((COUNT(CASE WHEN SS.Status = 3
              THEN SS.ID END)/COUNT(SS.ID))*100,2) AS PercentProcessing
    FROM DBO.WorkingSession AS SS
    JOIN DBO.Location AS LOC ON SS.LocationID = LOC.LocationID AND SS.BranchCode = LOC.BranchCode
    JOIN DBO.Status AS ST ON SS.Status = ST.ID
    GROUP BY LOC.SubCompanyNameVN, LOC.BranchName, LOC.BranchCode
    ORDER BY LOC.SubCompanyNameVN

標籤：sql sql-server 查询语句 存储过程 百分比

上一篇：從SQLServer中的customerId列中洗掉特殊字符

下一篇：視窗函式累積聚合