title: T-SQL - calculate percentage where denominator is all rows for a specific date
body: <p>I am trying to create columns to show percentages for each row where the denominator is the total for all rows in a given year.</p>
  <p>Currently the query I have created looks like this:</p>
  <pre><code>    SELECT
     pend,
     ReportingPeriod,
     Area,
     SubsCategoryId,
     gender,
     age_group,
     Ethnicity,
     AllInTx_Count,
     NewTx_Count
  FROM
     (
        SELECT
           p.pend,
           ReportingPeriod,
           p.Area,
           SubsCategoryId,
           gender,
           age_group,
           Ethnicity,
           AllInTx AS AllInTx_Count,
           NewTx AS NewTx_Count
        FROM
           (
              SELECT
                 pend,
                 Area,
                 CONVERT(char(4), DATEPART(yy, pend) - 1) + '/' + RIGHT(CONVERT(char(4), DATEPART(yy, pend)), 2) AS ReportingPeriod,
                 SubsCategoryId,
                 gender,
                 age_group,
                 'White' AS Ethnicity,
                 SUM(Eth_White_NewTx) AS NewTx,
                 SUM(Eth_White_AllInTx) AS AllInTx 
              FROM
                 dbo.ViewIt_Main 
              GROUP BY
                 pend,
                 Area,
                 SubsCategoryId,
                 gender,
                 age_group 
              UNION ALL
              SELECT
                 pend,
                 Area,
                 CONVERT(char(4), DATEPART(yy, pend) - 1) + '/' + RIGHT(CONVERT(char(4), DATEPART(yy, pend)), 2) AS ReportingPeriod,
                 SubsCategoryId,
                 gender,
                 age_group,
                 'Mixed/Multiple ethnic group' AS Ethnicity,
                 SUM(Eth_Mixed_NewTx) AS NewTx,
                 SUM(Eth_Mixed_AllInTx) AS AllInTx 
              FROM
                 dbo.ViewIt_Main AS ViewIt_Main_5 
              GROUP BY
                 pend,
                 Area,
                 SubsCategoryId,
                 gender,
                 age_group 
              UNION ALL
              SELECT
                 pend,
                 Area,
                 CONVERT(char(4), DATEPART(yy, pend) - 1) + '/' + RIGHT(CONVERT(char(4), DATEPART(yy, pend)), 2) AS ReportingPeriod,
                 SubsCategoryId,
                 gender,
                 age_group,
                 'Asian/Asian British' AS Ethnicity,
                 SUM(Eth_Asian_NewTx) AS NewTx,
                 SUM(Eth_Asian_AllInTx) AS AllInTx 
              FROM
                 dbo.ViewIt_Main AS ViewIt_Main_4 
              GROUP BY
                 pend,
                 Area,
                 SubsCategoryId,
                 gender,
                 age_group 
              UNION ALL
              SELECT
                 pend,
                 Area,
                 CONVERT(char(4), DATEPART(yy, pend) - 1) + '/' + RIGHT(CONVERT(char(4), DATEPART(yy, pend)), 2) AS ReportingPeriod,
                 SubsCategoryId,
                 gender,
                 age_group,
                 'Black/African/Caribbean/Black British' AS Ethnicity,
                 SUM(Eth_Black_NewTx) AS NewTx,
                 SUM(Eth_Black_AllInTx) AS AllInTx 
              FROM
                 dbo.ViewIt_Main AS ViewIt_Main_3 
              GROUP BY
                 pend,
                 Area,
                 SubsCategoryId,
                 gender,
                 age_group 
              UNION ALL
              SELECT
                 pend,
                 Area,
                 CONVERT(char(4), DATEPART(yy, pend) - 1) + '/' + RIGHT(CONVERT(char(4), DATEPART(yy, pend)), 2) AS ReportingPeriod,
                 SubsCategoryId,
                 gender,
                 age_group,
                 'Other ethnic group' AS Ethnicity,
                 SUM(Eth_Other_NewTx) AS NewTx,
                 SUM(Eth_Other_AllInTx) AS AllInTx 
              FROM
                 dbo.ViewIt_Main AS ViewIt_Main_2 
              GROUP BY
                 pend,
                 Area,
                 SubsCategoryId,
                 gender,
                 age_group
           )
           AS p 
     )
     AS f
  </code></pre>
  <p>Below is a screenshot showing the current output of this query:</p>
  <p><a href="https://i.stack.imgur.com/8IWPZ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8IWPZ.png" alt="enter image description here" /></a></p>
  <p>I need the denominator to be the sum of all Ethnicities in a given pend for the SubsCategoryId, Area, gender and age_group.</p>
  <p>Like current output I need to create two columns one for the proportion of AllInTx and another for NewTx.</p>
  <p>How do I modify the query to achieve this?</p>