use fields from different tables group by date to calculate percentage
Hi, I am trying to calculate a percentage using two fields from two different tables in SQL Magic Transformation.
a looks like this:
Date All Revenue 09/01/21 100 09/02/21 200
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! :)