# 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.

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

Tagged:

• Coach

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`
```
**Did this solve your problem? Accept it as a solution!**
• Coach

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.

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...

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"

• Coach

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`
```
**Did this solve your problem? Accept it as a solution!**
• Coach

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.

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...