Issues with SUM Function within SQL Transformations

martinez473
martinez473 Member
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:

Comments

  • 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

    @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)

Categories