use fields from different tables group by date to calculate percentage

user095063
user095063 Member
in APIs & Domo Developer

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:

Best Answers

  • GrantSmith
    GrantSmith Coach
    edited November 2021 Answer ✓

    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`
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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

    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"

Answers

  • GrantSmith
    GrantSmith Coach
    edited November 2021 Answer ✓

    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`
    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Coach
    Answer ✓

    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

    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"

Categories