use fields from different tables group by date to calculate percentage

user095063

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! :)

GrantSmith

Hi @user095063

You need to join your two tables together since you have one table for all your revenue and another for your HDM revenue. Try something like this:

select `a`.`Date`, SUM(`b`.`HDM Revenue`) / SUM(`a`.`All Revenue`)
FROM `a`
LEFT JOIN `b` ON `a`.`Date` = `b`.`Date`
GROUP BY `a`.`Date`