How can i get a SUM of a column from a table ...

references
(Geoff Ringent) #1

How can i get a SUM of a column from a table that contains the reference key to a parent table in a one to many relationship??

In SQL it would be something like:

SELECT SUM([Price]) from tblInvoice WHERE tblCUSTOMER([CustId]) = tblINVOICE([CustId])

(Levent KULAÇOĞLU) #2

@Geoff_Ringent

SUM(SELECT(INVOICE[Price],[CustId]=[_THISROW].[CustId]))

(Geoff Ringent) #3

Thanks! :slight_smile:

(Geoff Ringent) #4

Thanks for that Levent … but i cannot seem to find how to use that expression to get what i want … which is this:

I have a spreadsheet with two tabs (tables) … first tab contains the main record which has DATE as Primary Key … the next tab has child records with that DATE as a Foreign Key (along with its own PK of course).

In my app i have been able to create a slice where it returns the single header row for TODAY() … together with the detailed child records belonging to the second tab!!

But where i am now stumped is being able to SUM the financial number column in the child table for that particular header date … and then being able to show that summation in the header record … as some ‘virtual field’ i suppose?? … so that the user can always see what the running total for the particular day is.

Can this be done?? If so any tips would be appreciated.

Thankyou in advance.

(Levent KULAÇOĞLU) #5

SUM([RelatedRecords][Price])