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`

jaeW_at_Onyx

I would not get into the habit of JOINING fact tables this will lead to heartache and row growth. if you continue down this path you tend to end up with a ton of single use datasets that only work for the card they power.

https://kimballgroup.forumotion.net/t667-joining-two-fact-tables

instead UNION the two tables together (usually I like to differentiate with an Activity Type column.

then you can take a nice clean sum(colA)/ sum(colB) in Analyzer.

even though it seems simple, @GrantSmith 's GROUP BY clause means that you no longer have any drill down capability. That's super limiting for your business users...

https://www.youtube.com/watch?v=PVbOeLSae9o&t=820s

amehdad

Hi @user095063, have you tried adding MAX before 'HDM Revenue'?

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`

jaeW_at_Onyx

I would not get into the habit of JOINING fact tables this will lead to heartache and row growth. if you continue down this path you tend to end up with a ton of single use datasets that only work for the card they power.

https://kimballgroup.forumotion.net/t667-joining-two-fact-tables

instead UNION the two tables together (usually I like to differentiate with an Activity Type column.

then you can take a nice clean sum(colA)/ sum(colB) in Analyzer.

even though it seems simple, @GrantSmith 's GROUP BY clause means that you no longer have any drill down capability. That's super limiting for your business users...

https://www.youtube.com/watch?v=PVbOeLSae9o&t=820s