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

[PaulD1]

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

[v-yamao-msft · Accepted solution]

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:

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

Best regards,
Mabel Mao

[PaulD1]

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

[moutinhoabreu]

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

---

[PaulD1]

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.

[moutinhoabreu]

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 Jsontransform data query