create a calculated column base on two column in SQL-CodePudding

I have a below table and I need to create a calculated column (RA) based on the category and month column.

    Oa  Sa  Ai  month   MDY
    5   10  2   Jan     J302022
    16  32  38  Jan     J302022
    15  14  4   Feb     J302022
    46  32  81  Jan     J302022
    3   90  0   Mar     J302022
    51  10  21  Jan     J302021
    19  32  3   Jan     J302021
    45  16  41  Feb     J302021
    46  7   81  Jan     J302021
    30  67  14  Mar     J302021
    45  16  41  Apr     J302021
    46  7   81  Apr     J302021
    30  67  0   Jan     J302021
    56  17  0   Mar     J302022

first, it should need to consider a category, for example, J302022, then it needs to calculate the "RA" column based on the month for that category. for example, J302022, Jan, ((5 16 46 46) (10 32 32 7)) / (2 38 81 81) = 0.96. So below is the expected output looks like.

    Oa  Sa  Ai  month   category    RA
    5   10  2   Jan     J302022     0.96
    16  32  38  Jan     J302022     0.96
    15  14  4   Feb     J302022     7.25
    46  32  81  Jan     J302022     0.96
    3   90  0   Mar     J302022     0
    51  10  21  Jan     J302021     8.70
    19  32  3   Jan     J302021     8.70
    45  16  41  Feb     J302021     1.48    
    46  7   81  Jan     J302022     0.96
    30  67  14  Mar     J302021     6.92
    45  16  41  Apr     J302021     1.48
    46  7   81  Apr     J302022     0.65    
    30  67  0   Jan     J302021     8.70
    56  17  0   Mar     J302022     0

Is it possible to do it in SQL?

Thanks in advance!

CodePudding user response：

select Oa, Sa, Ai, month, category, 
  coalesce((ra1 ra2)/ra3, 0) as RA 
from (
  select Oa, Sa, Ai, month, mdy as category, 
    sum(oa) over (partition by month, mdy) as ra1, 
    sum(sa) over (partition by month, mdy) as ra2, 
    sum(ai) over (partition by month, mdy) as ra3 
  from WhateverYourTableNameIs
) as t;

Output on MySQL 8.0.29:

 ------ ------ ------ ------- ---------- -------- 
| Oa   | Sa   | Ai   | month | category | RA     |
 ------ ------ ------ ------- ---------- -------- 
|   45 |   16 |   41 | Apr   | J302021  | 0.9344 |
|   46 |    7 |   81 | Apr   | J302021  | 0.9344 |
|   45 |   16 |   41 | Feb   | J302021  | 1.4878 |
|   15 |   14 |    4 | Feb   | J302022  | 7.2500 |
|   51 |   10 |   21 | Jan   | J302021  | 8.7083 |
|   19 |   32 |    3 | Jan   | J302021  | 8.7083 |
|   30 |   67 |    0 | Jan   | J302021  | 8.7083 |
|    5 |   10 |    2 | Jan   | J302022  | 0.9604 |
|   16 |   32 |   38 | Jan   | J302022  | 0.9604 |
|   46 |   32 |   81 | Jan   | J302022  | 0.9604 |
|   46 |    7 |   81 | Jan   | J302022  | 0.9604 |
|   30 |   67 |   14 | Mar   | J302021  | 6.9286 |
|    3 |   90 |    0 | Mar   | J302022  | 0.0000 |
|   56 |   17 |    0 | Mar   | J302022  | 0.0000 |
 ------ ------ ------ ------- ---------- --------

CodePudding user response：

for SQL Server

select Oa, Sa, Ai, [Month],Category,
case when (sum(Ai) over(partition by [Month], Category) *1.0) = 0 then 0 else 
(sum(Oa) over(partition by [Month], Category)  
sum(Sa) over(partition by [Month], Category))/
(sum(Ai) over(partition by [Month], Category) *1.0) end Ra
from #temp
order by Category desc

1.0 is multiplied in denominator to convert the output to float.