sum() function with Execute SQL

[rekent]

I have a field in a portal named "Assistance | Amount" in a table named "Assistance" that I want to summarize when "Assistance | Church" is "fbcec" and the entries are during a specified date range.  I have a count of the occurrences of such a record using the COUNT(*) function, but when I modify the code to give me a sum of the dollar amounts, the field populates with a question mark.  

 

Granted, I am not extremely proficient with SQL, but I have looked over this a few times and compared it to the working code I have and the proper syntax for SUM() and I am not sure where my error is.  

 

Does anyone more familiar with SQL possibly see where I messed up?

Let ( [
~cd = Get ( CurrentDate ) ;
~sd = Date ( Month ( ~cd ) ; Day ( ~cd ) ; Year ( ~cd ) - 1 ) ;

~sql = 
" SELECT SUM (Assistance | Amount)
FROM Assistance 
WHERE ( "Assistance | Type" = ?  ) AND ( "Assistance | Church" = ?  ) AND
"Assistance | Date" BETWEEN ? AND ? " ;

~cost = ExecuteSQL ( ~sql ; "" ; "" ; "Gas" ; "fbcec" ; ~sd ; ~cd )
] ;
~cost 
)

[eos]

" SELECT SUM (Assistance | Amount)

 

The field name is not quoted. In general, it's recommended to Custom Functions to protect against field name changes, and pre-emptively quote the field names in the process.

 

So try

Let ( [
  ~cd = Get ( CurrentDate ) ;
  ~sd = Date ( Month ( ~cd ) ; Day ( ~cd ) ; Year ( ~cd ) - 1 ) ;
  ~sql = "
    SELECT SUM ( "Assistance | Amount" )
    FROM Assistance 
    WHERE 
      "Assistance | Type" = ? AND 
      "Assistance | Church" = ? AND
      "Assistance | Date" BETWEEN ? AND ? 
    " ;
  ~cost = ExecuteSQL ( ~sql ; "" ; "" ; "Gas" ; "fbcec" ; ~sd ; ~cd )
  ] ;
  ~cost 
)

Apart from that, maybe use some less complicated field names – why repeat the table name?

[rekent]

Perfect, thanks! I had a feeling it was me doing/missing something stupid...

 

The table name repeat is because some of the fields in the table, for example church name, can occur in another table and have different values. At the time this was made, that seemed the easiest way of differentiating between them and keeping everything straight. In retrospect, it could have been done better.

[Kris M]

eos nailed it... The | (pipe) operator in several dialects of SQL is the bitwise or operator. PS if youve got the same field in two different tables you should look into normalization.

[rekent]

The above SQL is determining the values I need perfectly, but I noticed a potential issue.  The code is used as the calculation for a calculation field, but I am required to specify a number of iterations.  Is there a way to avoid that so the calculation simply computes for all records? I realize I could set it for an extremely high number of iterations, but my assumption is that would result in poor performance and sluggish response.  On the other side, if the number of iterations is set too low and the number of records in the database exceeds that number, I will no longer have an accurate result.

[eos]

but I am required to specify a number of iterations.

 

Where are you required to do that? What exactly is your scenario?

[rekent]

I have a field that has a Calculation type, with the result being an un-stored number.  It has a field for the number of repetitions for the calculation to perform.  I have attached a picture so you can see it more clearly than my terrible explanation.

 

 

[eos]

So why are you using a repeating field with hundreds of repetitions (that will all have the same result)?

 

Using a repeating field for a calculation usually involves utilizing the repetition number to simulate a loop, and populate the repetitions with the iteration-specific results. Is there any need for that here (by year, by type)?

 

And wouldn't then maybe a report be the better choice?

[rekent]

I am a bit new to this, so perhaps I am going about it the wrong way.  I increased the repetitions to a rather large number because at 1 repetition it did not calculate correctly, but at many repetitions it does.

 

I have a multiple records, each representing a single person.  Within each record the is a portal that contains fields to record various forms of aid such as gas, utilities, etc., and a source of the aid.  I want to determine a sum across all the records of a specific type of aid for a given source in the past 365 days and then place all the results on a single sheet that can be presented to show all activity over the past year.

 

Is there a simpler and/or better way to go about this?  If it matters, I am also wanting to chart the results (for example, a pie chart listing gas, utilities, and then a single field combining anything that is not gas or utilities).

[comment]

I want to determine a sum across all the records of a specific type of aid for a given source in the past 365 days and then place all the results on a single sheet that can be presented to show all activity over the past year.

 

IMHO, the simplest way to achieve this would be to put SQL aside and return to Filemaker's native reporting method - i.e. go to the Assistance table, find the records in the past year, sort them by type and show them in a summary layout (having a sub-summary part by type and no body part). Use a summary field to total the assistance amounts.

 

gas, utilities, and then a single field combining anything that is not gas or utilities).

For this, you need to use a calculation field cType =

Case ( Type = "Gas" or Type = "Utilities" ; Type ; "Other" )

and then sort and summarize by this field instead of the original Type.