Issues with SUM Function within SQL Transformations

martinez473
martinez473 Member
edited May 7 in SQL DataFlows

Hello everyone,

I've been encountering issues with the SUM() function within my SQL transformations. I've imported large datasets and added my own calculations (as shown in the attached photos). When I try to query the three mentioned columns and group the results by month, specifically using SUM(average meals...), SUM(total reimbursable...), and SUM(participation), my participation column returns values over 100%. However, the way the calculation is written, it should return a value less than 100% for each month.

Interestingly, when I pull the raw data with the individual row calculations (without using the SUM() function), my numbers are correct. I have another dataset that I'm using to compare my numbers, and the discrepancy only arises when I query with the SUM() function.

I'm unsure if I've written my calculation incorrectly, but it seems to follow the calculations that were provided to me. Any help would be greatly appreciated! I can provide more information if needed.


sql1.png sql2.png
Tagged:

Best Answer

  • MichelleH
    MichelleH Coach
    Answer ✓

    @martinez473 It looks like the issue is that you're calculating the percentage before rolling up on a monthly level, so your card is adding up the percentages of each row of your dataset. I'd recommend changing the Meals per Day and Participation to beast modes within your card that calculate percentages based off the totals instead of individual rows.

    Meals per Day

    SUM(`Total Reimbursable Meals`)/sum(`Serving Days`)

    Participation

    (SUM(`Total Reimbursable Meals`)/sum(`Serving Days`))/NULLIF(`20th Day`,0)

Answers

  • MichelleH
    MichelleH Coach

    @martinez473 Would it be possible to see an anonymized sample of your data, as well of some examples of what you're seeing versus what you're expecting?

  • martinez473
    martinez473 Member

    Hi, @MichelleH
    I have attached two files, one for August 2024 and one for October 2024. I have also attached a picture of the pivot table that contains the correct numbers I am trying to match. This pivot table displays the grand total and extends to April 2025. Additionally, I have attached my Domo-created table, which goes up to March 2025. As you can see, the participation numbers are incorrect. Finally, I have attached a picture of my filtering within Domo's SQL Transformations, where I have removed unnecessary information.  

    meal participation actual.png meal participation mine.png domo code removing blank data.png


    24oct.xls
    aug24.xls
  • MichelleH
    MichelleH Coach
    Answer ✓

    @martinez473 It looks like the issue is that you're calculating the percentage before rolling up on a monthly level, so your card is adding up the percentages of each row of your dataset. I'd recommend changing the Meals per Day and Participation to beast modes within your card that calculate percentages based off the totals instead of individual rows.

    Meals per Day

    SUM(`Total Reimbursable Meals`)/sum(`Serving Days`)

    Participation

    (SUM(`Total Reimbursable Meals`)/sum(`Serving Days`))/NULLIF(`20th Day`,0)
  • martinez473
    martinez473 Member

    @MichelleH Thank you for your help! While participation looks good for individual sites, I'm now facing an issue with adding or grouping percentages by month. I haven't found a feature to calculate these. My attempt to adjust the code with SUM(SUM(`Total Reimbursable Meals`)/SUM(`Serving Days`))/NULLIF(`20th Day`,0)) didn't work. The pivot table also didn't provide this functionality. Is there a way to aggregate data across my individual sites?

    Screenshot 2025-05-07 112629.png

Categories