Calculate percentage while select

I have a stored procedure:

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

Result:

SubCompanyNameVN	BranchName	Total	CountNotProcessedYet	CountProcessing
Vùng 1	HNI_01	5	3	2
Vùng 1	HNI_02	15	5	10
Vùng 1	HNI_07	12	6	6

But my expect result is:

SubCompanyNameVN	BranchName	Total	CountNotProcessedYet	percentNotProcessedYet	CountProcessing	percentProcessing
Vùng 1	HNI_01	5	3	60%	2	40%
Vùng 1	HNI_02	15	5	33.33%	10	66.67%
Vùng 1	HNI_07	12	6	50%	6	50%

So I update my stored procedure like this:

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

Error:

Is there any way I can calculate the percentage?

CodePudding user response：

Firstly on the error message you are getting, you can't reference to the column alias like that on the same level.

CountProcessing/Total

You can either use a CTE or derived query to calculate Total and CountProcessing and then calculate the Percent on the outer query using the column alias.

You can simplified your query using CASE expression instead of sub-query

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

CodePudding user response：

You can not nickname a column and use the same name for a computational column at the same time in one step of the process. Change your query as shown below

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

CodePudding user response：

Maybe like this

    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