Hi, I am trying to calculate a percentage using two fields from two different tables in SQL Magic Transformation.
Table a
looks like this:
Date All Revenue
09/01/21 100
09/02/21 200
Table b
looks like this:
Date HDM Revenue
09/01/21 90
09/02/21 170
I need to build a table that would calculate the percentage of HDM Revenue, with the following formula: (`Sum HDM Revenue` * 100) / `Sum All Revenue`
The desired output is:
Date HDM Percentage
09/01/21 90
09/02/21 85
When I run the following query I have an error: `Subquery returns more than 1 row.`
SELECT `Date`,
((select `HDM Revenue` from `b`) * 100) / `All Revenue` as `dfp HDM Percentage`
from `a`
group by `Date`
I also tried to group the subquery by Date
. How would I indicate the corresponding rows?
Thank you! :)