SQL Custom Query

Question & Answer
,
1

Hello - I have a table with data that looks like this:

|year - string | month - string | Answer - string | Weight - float|
| 2022 | 12 | Y | 20 |
| 2022 | 12 | N | 15 |
| 2022 | 11 | Y | 11 |
| 2022 | 11 | N | 30 |

I am trying to use SQL Custom Query to output a table that calculates a field ‘Rate’ which is equal to:

Rate = Weight N / (Weight N + Weight Y) for each date where Answer = N

I have calculated a field ‘Date’ already

The end result should look something like this:

|year - string | month - string | Date - Date| Answer - string | Weight - float | Rate - float |
| 2022 | 12 | 2022/12/1 | Y | 20 | |
| 2022 | 12 |. 2022/12/1 | N | 15 | (15)/(15+20) = 0.428 |
| 2022 | 11 | 2022/11/1 | Y | 11 | |
| 2022 | 11 | 2022/11/1 | N | 30 | (30)/(11+30) = 0731 |

2

Hello @isabella !

Will there always be only 2 rows of the same date in the table (1 Answer: Y, and 1 Answer: N)?

If yes, you could try making 2 columns “Yes_weight” and “No_weight” and have a single row for each date. Then use a calculated field that would do (15)/(15+20) = 0.428 like this: {No_weight}/({no_weight}+{yes_weight}), so it will calculate every row.

Let me know if this helped!

3

@isabella - In case you are looking to do this at the SQL level then you have to use Window function with Year and Month as the Partition column. Also, you have to use case - when - then - else - end construct to only calculate when the “Answer” column value is “N”. Sharing a generic link of SQL Window Function reference. Please validate independently according to your database, but the concept would remain same. Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!