cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulD1
Super User
Super User

Get the sum of a column in an Azure SQL DB table

I'm new to Flow so apologies if there is something obvious I am missing.

 

I have managed to create a flow with daily recurrence that retrieves the current day's 'OrderHeader' data from a table in an Azure SQL DB. This data is then placed in an email as an HTML table.

 

The data includes a column 'OrderHeaderPaid' and I would like to include the sum of this value in the email.

 

I appear to only be able to show data from tables, not views (using SQL Get Rows). I looked at trying to add an OData aggregate in the Aggregate Transformation option of the SQL Get Rows action as follows:

/Order.OrderHeader?$apply=aggregate(OrderHeaderPaid with sum as SumOfOrderHeaderPaid)

The flow 'compiles' and tries to execute the Get Rows action four times and then fails.

 

I was thinking of using an 'Apply to Each' loop to get the value of each record and add it to a variable, but that doesn't seem a very efficient approach.

 

How can I simply add the sum of the current day's OrderHeaderPaid values into the email? 

 

Thanks

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
v-yamao-msft
Community Support
Community Support

Hi PaulD1,

 

Per my certain test, it seems that we don’t have a way to calculate the sum of the items get through the action “Get rows”. We could set the items to an email as an HTML table.


What I did likes below:
3.PNG


I will keep on to see if there is a workaround on this issue.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

5 REPLIES 5
v-yamao-msft
Community Support
Community Support

Hi PaulD1,

 

Per my certain test, it seems that we don’t have a way to calculate the sum of the items get through the action “Get rows”. We could set the items to an email as an HTML table.


What I did likes below:
3.PNG


I will keep on to see if there is a workaround on this issue.

 

Best regards,
Mabel Mao

Community Support Team _ Mabel Mao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Thanks @v-yamao-msft 

 

Yes, I am already formatting the data returned by GetRows as HTML so I get a nice tabular format for our data which includes a Sales column.

 

Please let me know if you come across a way to calculate the SUM (or other aggregates) of a column returned by GetRows so I can include the Total, ideally in the HTML table but failing that, just within the body of the email message as I know this is something users will immediately be asking for!

 

Thanks

Paul

Were you able to find out a way to get the sum of a get rows Sql column |?

TKs 


@PaulD1 wrote:

Thanks @v-yamao-msft 

 

Yes, I am already formatting the data returned by GetRows as HTML so I get a nice tabular format for our data which includes a Sales column.

 

Please let me know if you come across a way to calculate the SUM (or other aggregates) of a column returned by GetRows so I can include the Total, ideally in the HTML table but failing that, just within the body of the email message as I know this is something users will immediately be asking for!

 

Thanks

Paul


 

Hi @moutinhoabreu

 

In this case, I solved the problem by using an 'An Apply to Each' loop to add each individual column value to a variable (my result set was only ever a dozen or so rows).

Were I to have the same issue again, I think I would use a View on the SQL side that includes totals (using either RollUp or Grouping) or just add another SQL action in the Flow that calls a View holding just the total and add that into the html of my email below the html table showing the individual values.

Hi @PaulD1,

 

Got another workaround using the new " Transform Data using power query ".

Got the table, selected the fields and filtered by date "eg. Today" and at the end applied the Calculated Sum.

In the example is "null" because for today on my Sql there's no data. But during the weekdays it gave me a calculated Sum.

My flow was to trigger an email to a Carrier with several options: 

1) if Invoice table is null then send email canceling pickup

2) if invoice table has data then Sum the number of cartoons:

2.1) If calculated sum is under 30 cartoons then do nothing

2.2) If calculated sum is above 30 cartoons the send email to the carrier alerting the need for an extra car to manage the pickup.

 

The only problem i'm facing now is with the "null" response. Because i have a Json right after the " Transform Data using power query " to isolate the calculated Sum and it deals an error when the returns is "null". 

" Transform Data using power query " returns a string "weird one" with text and the calculated sum inside that string. So you need to apply a parse Json.

Parse JsonParse Jsontransform data querytransform data query

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (2,368)