SQL calculating percentage over COUNT

Question

I have the following query within SQL

    SELECT  
    ISNULL(Status,'Grand Total') as Status,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 30) AND GETDATE() THEN 1 END) AS b1,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 60) AND (GETDATE() - 30) THEN 1 END) AS b2,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 90) AND (GETDATE() - 60) THEN 1 END) AS b3
    FROM PRPC7.prpcdata.DLL_index_ContractMgtWork
    WHERE CaseType = 'Amortization Schedule DE'
    GROUP BY Status WITH ROLLUP

The query executes the following result:

    Status              b1  b2  b3
    ------------------------------
    Open-LetterReview   1   7   11
    Open-RequestAMO     4   3   4
    Pending-ExceptionWB 0   2   10
    Resolved-Success    7   1   0
    Resolved-Withdrawn  0   0   0
    Grand Total         12  13  25

I want to achieve the %'s of b1,b2,b3 over the grand total:

    Status              b1  b2  b3    %b1      %b2     %b3
    --------------------------------------------------------
    Open-LetterReview   1   7   11    8,3%     53,8%   44%
    Open-RequestAMO     4   3   4     33,3%    
    Pending-ExceptionWB 0   2   10    0% 
    Resolved-Success    7   1   0     58,3%
    Resolved-Withdrawn  0   0   0     0%
    Grand Total         12  13  25    100%

Thanks in advance~

Following the solution of this post How to calculate percentage with a SQL statement seems to not work for me. I assume it has to do with the fact I use a "case when", If I follow this answer my query should look like this:

    SELECT  
    ISNULL(Status,'Grand Total') as Status,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 30) AND GETDATE() THEN 1 END) AS b1,
    (COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 30) AND GETDATE() THEN 1 END) * 100 / (Select count(*) From PRPC7.prpcdata.DLL_index_ContractMgtWork)) as pct,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 60) AND (GETDATE() - 30) THEN 1 END) AS b2,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 90) AND (GETDATE() - 60) THEN 1 END) AS b3
    FROM PRPC7.prpcdata.DLL_index_ContractMgtWork
    WHERE CaseType = 'Amortization Schedule DE'
    GROUP BY Status WITH ROLLUP

    Status              b1 pct b2   b3
    ----------------------------------
    Open-LetterReview   1   0   7   11
    Open-RequestAMO 4   0   3   4
    Pending-ExceptionWB 0   0   2   10
    Resolved-Success    7   0   1   0
    Resolved-Withdrawn  0   0   0   0
    Grand Total         12  0   13  25

Possible duplicate of [How to calculate percentage with a SQL statement](https://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement) — Khan Abdulrehman, Oct 03 '17 at 09:23
The top answer in that doesn't work, I assume it has to do with the fact I'm using a "case when" within my count, I'll update the post — R100, Oct 03 '17 at 09:57

score 0 · Answer 1 · answered Oct 03 '17 at 09:19

Something like this may give you the output,

WITH MY_DATA AS (
    SELECT  
    ISNULL(Status,'Grand Total') as Status,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 30) AND GETDATE() THEN 1 END) AS b1,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 60) AND (GETDATE() - 30) THEN 1 END) AS b2,
    COUNT(CASE WHEN pxCreateDateTime BETWEEN (GETDATE() - 90) AND (GETDATE() - 60) THEN 1 END) AS b3
    FROM PRPC7.prpcdata.DLL_index_ContractMgtWork
    WHERE CaseType = 'Amortization Schedule DE'
    GROUP BY Status WITH ROLLUP
)
SELECT Status, b1, 
b1 / (select b1 from MY_DATA where status = 'Grand Total'),
b2 / (select b2 from MY_DATA where status = 'Grand Total'), 
b3 / (select b3 from MY_DATA where status = 'Grand Total')
FROM MY_DATA

SQL calculating percentage over COUNT

1 Answers1