Divide by Total in Group By Function

Question

I want to put % of Total for each of my column on my code below, but I could not get my head around it as the SELECT statement already filtered by Group By

SELECT CCK_DIALEDTHEME AS 'Theme',
       COUNT(TA_TASKID) AS 'Total Calls Incoming',
       COUNT(CASE WHEN TM_SPEECH > 0 THEN TA_TASKID END) AS 'Total Answered Calls',
       COUNT(CASE WHEN TM_SPEECH = 0 THEN TA_TASKID END) AS 'Total Lost Calls'
  FROM [BSIT_ERA_RAW_DATA].[dbo].[IPOCC_LEVELONEDATA]
  WHERE TA_TIMESTAMP >= dateadd(day,-1, cast(getdate() as date)) AND TA_TIMESTAMP <= dateadd(day,0, cast(getdate() as date))
        AND CCK_ROUTEDTHEME LIKE '%_T'
        AND TA_CONNECTIONSTATE LIKE '%QUEUE%'
  GROUP BY CCK_DIALEDTHEME

UNION ALL

SELECT 'Total' AS 'Theme',
       COUNT(TA_TASKID) AS 'Total Calls Incoming',
       COUNT(CASE WHEN TM_SPEECH > 0 THEN TA_TASKID END) AS 'Total Answered Calls',
       COUNT(CASE WHEN TM_SPEECH = 0 THEN TA_TASKID END) AS 'Total Lost Calls'
  FROM [BSIT_ERA_RAW_DATA].[dbo].[IPOCC_LEVELONEDATA]
  WHERE TA_TIMESTAMP >= dateadd(day,-1, cast(getdate() as date)) AND TA_TIMESTAMP <= dateadd(day,0, cast(getdate() as date))
        AND CCK_ROUTEDTHEME LIKE '%_T'
        AND TA_CONNECTIONSTATE LIKE '%QUEUE%'

Below is the Result:

  Theme             Total Calls Incoming    Total Answered Calls    Total Lost Calls
  Adelaide_T        109                     103                     6 
  Brisbane_T        591                     573                     18 
  Canberra_T        10                      9                       1
  CentralCoast_T    46                      45                      1 
  Geelong_T         2                       2                       0 
  Melbourne_T       621                     588                     33
  MELNight_T        7                       0                       7 
  Sydney_T          598                     585                     13 
  SYDNight_T        3                       0                       3 
  Tasmania_T        1                       0                       1
  Woollongong_T     1                       1                       0   
  Total             1989                    1906                    83

I want something like this:

     Theme              Total Calls Incoming  VS Total%     Total Answered Calls  VS Total%   Total Lost Calls  VS Total%
     Adelaide_T         109                   5.48%         103                   5.40%       6                 7.23%

Cheers

Answer 1

I modify my code to have sub-query for total value, in order to work out the % of each theme to the total value, below one of the example:

SELECT CCK_DIALEDTHEME AS 'Theme',
       COUNT(TA_TASKID) AS 'Total Calls Incoming',
       STR(((100.00* 
       (COUNT(TA_TASKID))) 
       / 
       (SELECT COUNT(TA_TASKID) 
         FROM [BSIT_ERA_RAW_DATA].[dbo].[IPOCC_LEVELONEDATA]
         WHERE TA_TIMESTAMP >= dateadd(day,-1, cast(getdate() as date)) AND TA_TIMESTAMP <= dateadd(day,0, cast(getdate() as date))
         AND CCK_ROUTEDTHEME LIKE '%_T'
         AND TA_CONNECTIONSTATE LIKE '%QUEUE%')),5,2) AS '% Total'

Result will include the % Total column:

Theme           Total Calls Incoming    % Total
Adelaide_T      109                     5.48
Brisbane_T      591                     29.71
Canberra_T      10                      0.50
CentralCoast_T  46                      2.31
Geelong_T       2                       0.10
Melbourne_T     621                     31.22
MELNight_T      7                       0.35
Sydney_T        598                     30.07
SYDNight_T      3                       0.15
Tasmania_T      1                       0.05
Woollongong_T   1                       0.05

Answer 2

Use sum() over () to find total. Then calculate percentage per row. myTable should be replaced with your query

select
    Theme, [Total Calls Incoming]
    , [VS Total%] = cast(cast(100.0 *[Total Calls Incoming] / sum([Total Calls Incoming]) over () as decimal(20, 2)) as varchar(25)) + '%'
    , [Total Answered Calls]
    , [VS Total%] = cast(cast(100.0 *[Total Answered Calls] / sum([Total Answered Calls]) over () as decimal(20, 2)) as varchar(25)) + '%'
    , [Total Lost Calls]
    , [VS Total%] = cast(cast(100.0 *[Total Lost Calls] / sum([Total Lost Calls]) over () as decimal(20, 2)) as varchar(25)) + '%'
from
    myTable

Here is how query should look like:

SELECT
    Theme, [Total Calls Incoming]
    , [VS Total%] = cast(cast(100.0 *[Total Calls Incoming] / (sum([Total Calls Incoming]) over () / 2) as decimal(20, 2)) as varchar(25)) + '%'
    , [Total Answered Calls]
    , [VS Total%] = cast(cast(100.0 *[Total Answered Calls] / (sum([Total Answered Calls]) over () / 2) as decimal(20, 2)) as varchar(25)) + '%'
    , [Total Lost Calls]
    , [VS Total%] = cast(cast(100.0 *[Total Lost Calls] / (sum([Total Lost Calls]) over () / 2) as decimal(20, 2)) as varchar(25)) + '%'
FROM (
    SELECT
        ISNULL(CCK_DIALEDTHEME, 'Total') AS 'Theme',
        COUNT(TA_TASKID) AS 'Total Calls Incoming',
        COUNT(CASE WHEN TM_SPEECH > 0 THEN TA_TASKID END) AS 'Total Answered Calls',
        COUNT(CASE WHEN TM_SPEECH = 0 THEN TA_TASKID END) AS 'Total Lost Calls'
    FROM 
        [BSIT_ERA_RAW_DATA].[dbo].[IPOCC_LEVELONEDATA]
    WHERE 
        TA_TIMESTAMP >= dateadd(day,-1, cast(getdate() as date)) AND TA_TIMESTAMP <= dateadd(day,0, cast(getdate() as date))
        AND CCK_ROUTEDTHEME LIKE '%_T'
        AND TA_CONNECTIONSTATE LIKE '%QUEUE%'
    GROUP BY GROUPING SETS (CCK_DIALEDTHEME, ())
) T