SQL Server Calculate the percentage of a column base on year

zzwlnbp8 于 2023-06-04 发布在其他

关注(0)|答案(1)|浏览(175)

I want to get the percentage of column Quantity based on the same year.
| ID | Model | Year | Qty |
| ------------ | ------------ | ------------ | ------------ |
| 1 | M1 | 2020-01-01 | 10 |
| 2 | M2 | 2020-02-07 | 12 |
| 3 | M3 | 2020-11-20 | 7 |
| 4 | M1 | 2021-01-04 | 8 |
| 5 | M2 | 2021-05-19 | 5 |
| 6 | M3 | 2021-09-11 | 11 |
| 7 | M4 | 2021-10-26 | 3 |

I have tried the below code but I'm not getting the desired result. Kindly assist

SELECT 
    Model,
    DATEPART(yy, Year) AS Year,
    SUM(Qty) AS Quantity,
    ROUND (100 * SUM(Qty) / MAX( SUMALL), 2) AS Percentage
FROM 
    tblStore
INNER JOIN 
    (SELECT SUM(Qty) SUMALL 
     FROM tblStore) A ON 1 = 1
GROUP BY 
    Model, Year

Expect output for my table should be like the one below

ID	Model	Year	Qty	Percentage
1	M1	2020-01-01	10	34.48
2	M2	2020-02-07	12	41.38
3	M3	2020-11-20	7	24.14
4	M1	2021-01-04	8	29.63
5	M2	2021-05-19	5	18.52
6	M3	2021-09-11	11	40.74
7	M4	2021-10-26	3	11.11

So if we add it manually we get in 2020 - 34.48 + 41.38 + 24.14 = 100% and | 2021 - 29.63 + 18.52 + 40.74 + 11.11 = 100%

sql-server

来源：https://stackoverflow.com/questions/76347087/calculate-the-percentage-of-a-column-base-on-year

1条答案

按热度按时间

avwztpqn1#

Something like this mayhaps:

select  *
,   qty / (0.01 * SUM(qty) over(partition by year(year))) as percentage
from (
    VALUES  (1, N'M1', N'2020-01-01', 10)
    ,   (2, N'M2', N'2020-02-07', 12)
    ,   (3, N'M3', N'2020-11-20', 7)
    ,   (4, N'M1', N'2021-01-04', 8)
    ,   (5, N'M2', N'2021-05-19', 5)
    ,   (6, N'M3', N'2021-09-11', 11)
    ,   (7, N'M4', N'2021-10-26', 3)
) t (ID,Model,Year,Qty)
order by id

Returned data:

ID	Model	Year	Qty	percentage
1	M1	2020-01-01	10	34.48275862068965
2	M2	2020-02-07	12	41.37931034482758
3	M3	2020-11-20	7	24.13793103448275
4	M1	2021-01-04	8	29.62962962962962
5	M2	2021-05-19	5	18.51851851851851
6	M3	2021-09-11	11	40.74074074074074
7	M4	2021-10-26	3	11.11111111111111

赞(0）回复(0）举报 2023-06-04

我来回答

SQL Server Calculate the percentage of a column base on year

1条答案

相关问题

热门标签

最新问答