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%
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 |