SQL Server Calculate Percentage from two rows

7cwmlq89 于 2022-12-10 发布在其他

关注(0)|答案(2)|浏览(142)

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.

sql-server

来源：https://stackoverflow.com/questions/74682264/calculate-percentage-from-two-rows

2条答案

按热度按时间

ujv3wf0j1#

如果只是这两列，我会简单地

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

赞(0）回复(0）举报 2022-12-10

tyu7yeag2#

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|

赞(0）回复(0）举报 2022-12-10

我来回答

SQL Server Calculate Percentage from two rows

2条答案

相关问题

热门标签

最新问答