Sum of all records within SQL Server on-premise

(0) Share

Report

Posted on by fchopo

8,003 Moderator

Hi,

I've been playing around Power Apps delegation. I'm trying to get the sum of all records within an SQL Server on-premise table called Orders, using the following expression:

Sum('[dbo].[Orders]';'Bank Comission')

The same expression is used to get the same data, but in this case stored in a SharePoint list. In this case, it shows a correct result (500, since "Bank Comission" value is equal to 1 for each row).

Any ideas on why the query against SQL Server On-Prem is displaying nothing? I've also checked the information about the SQL Connector: https://docs.microsoft.com/es-es/connectors/sql/

But get no conclusion.

Any help will be appreciated!

Regards,

Ferran

Categories:

Building Power Apps

All responses (4)

Answers (0)

Sort by

timl 37,287 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Ferran,

That's strange because the Sum function is delegable with SQL Server.

Do you have any null values in your 'Bank Comission' field?

To diagnose this further, do any of the other aggregate functions work correctly?

For example - Max('[dbo].[Orders]';'Bank Comission')
fchopo 8,003 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @timl for your quick answer.

Regarding your questions:

1) There are no null values in the 'Bank Comission' column, which has a value of 1 for each row.

2) I've tried using Average function and it doesn't work neither.

I agree with you that those aggregate functions are delegable, so I don't understand why no values are displayed.

Could it be because in decimal data type instead of an integer?

Regards,

Ferran
timl 37,287 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!

@fchopo

I've just tested the Sum function on a decimal field in my database and it works. Therefore, I doubt the reason it doesn't work for you is because of this data type.

Just to confirm, you're definitely retrieving records ok from your data source? If you add a data table control and set the Items property to '[dbo].[Orders]', the records appear and you can see a 'Bank Comission' value of 1 against each record?
fchopo 8,003 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hello @timl

Yes, it is working when displaying "Bank Comission" within a Gallery:

This is the table structure (I changed the Bank Comission data type from decimal to int just to do some testing):

By the way, I'm using SQL Server 2019 Standard Edition (SQL Server 15.0.2070.41) and SQL Server authentication. I've tried to sum or do an average on other columns (total cost, for instance), and it doesn't work either :S (really weird).

Any ideas?

Thanks anyway for your help @timl