sqlcasedivide

SQL divide row by row with the same column by using CASE

I want to create a new column which is to calculate the percentage, I know it can use subquery to do that,

but is it possible to use CASE to do this? If using CASE how is the logic?

Column A Column B Column C New Column that i want to create
2022-01-01 M0 72 72 * 100 / 72
2022-01-01 M1 53 53 * 100 / 72
2022-01-01 M2 29 29 * 100 / 72
2022-01-01 M3 18 18 * 100 / 72
2022-01-01 M4 2 2 * 100 / 72
2022-01-02 M0 102 102 * 100 / 102
2022-01-02 M1 80 80 * 100 / 102
2022-01-02 M2 72 72 * 100 / 102
2022-01-02 M0 32 32 * 100 / 102
2022-01-02 M0 14 14 * 100 / 102
...
...
...
Solution

  • Try this:

    SELECT T1.colC * 100 /T2.colC
FROM Table1 T1
INNER JOIN Table1 T2 
ON T1.colA = T2.colA
AND T2.ColB = 'M0'