创建一个列，该列计算前一列总价值的百分比

Question

下面我有一个查询，它按州给出了月总数，最后一列给出了年总数。我想创建一个列，按州给出总数的百分比。

这是我已经有的

SELECT STATE, 
ROUND(SUM(CASE WHEN LOADDATE = '2022-02-07' THEN TNET END),0) AS JAN_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-03-07' THEN TNET END),0) AS FEB_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-04-07' THEN TNET END),0) AS MAR_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-05-07' THEN TNET END),0) AS APR_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-06-07' THEN TNET END),0) AS MAY_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-07-07' THEN TNET END),0) AS JUN_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-08-07' THEN TNET END),0) AS JUL_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-09-07' THEN TNET END),0) AS AUG_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-10-07' THEN TNET END),0) AS SEP_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-11-07' THEN TNET END),0) AS OCT_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2022-12-07' THEN TNET END),0) AS NOV_TOTALREV,
ROUND(SUM(CASE WHEN LOADDATE = '2023-01-07' THEN TNET END),0) AS DEC_TOTALREV,
ROUND(SUM(TNET),0) AS YEAR_TOTALREV
FROM [dbo].example]
WHERE LOADDATE BETWEEN '2022-02-01' AND '2023-01-31'
GROUP BY STATE

下面是我希望最后两列的样子

tbody> <<tr>东北

STATE	YEAR_TOTALREV	Column B
AL	1000	10%
	3000	30%
纽约	6000	60%

正如Dale K所评论的，我们可以用窗函数计算所有状态和的总和:

SELECT STATE, 
ROUND(SUM(CASE WHEN LOADDATE = '2022-02-07' THEN TNET END), 0) AS JAN_TOTALREV,
. . .
ROUND(SUM(TNET), 0) AS YEAR_TOTALREV,
100.0 * SUM(TNET)
/ NULLIF(SUM(SUM(TNET)) OVER(), 0) AS PCT
FROM [dbo].example]
WHERE LOADDATE BETWEEN '2022-02-01' AND '2023-01-31'
GROUP BY STATE