Calculate Percentage from two rows-CodePudding

I am trying to get the percentage of two rows. I have a table1 as the below:

Year	Col1	Col2
Year 1	61	67
Year 2	56	75

I need to get the percentage of the above two rows as the below:

Year	Col1	Col2
Year 1 %	52.14%	47.18%
Year 2 %	47.86%	52.81%

I have used the below statement, but I am not able to get the percentage across like the above table:

SELECT 
  Year1
  ,[Col1], ([Col1] * 100) / (SELECT ISNULL(SUM([Col1]),0) FROM table1) AS Percentage
FROM table1
UNION ALL
SELECT 
  Year2
  ,Col2, ([Col2] * 100) / (SELECT ISNULL(SUM([Col2]),0) FROM table1) AS Percentage
FROM table1

Thanks in advance.

CodePudding user response：

If it just those 2 columns I would simply just do

SELECT
    Year,
    Col1,
    Col1 * 100.0 / (Col1   Col2) AS Col1Percentage,
    Col2,
    Col2 * 100.0 / (Col1   Col2) AS Col2Percentage
FROM table1

CodePudding user response：

Another approach is taking advantage of window function:

select year,
       (col1 * 100.0 / sum(col1) over ()) as col1_pct,
       (col2 * 100.0 / sum(col2) over ()) as col2_pct
  from table1;

outcome:

year  |col1_pct       |col2_pct       |
------ --------------- --------------- 
Year 1|52.136752136752|47.183098591549|
Year 2|47.863247863247|52.816901408450|