I would like to get a set of statistics from a table so that it shows values for each client once. There are multiple records for many of the clients, but the reporting requirements require results based on both all records and distinct by client. Here is the initial query:
SQLResult = ExecuteSQL (
"SELECT COUNT( DISTINCT a.\"ClientID\"), SUM (a.\"ServiceUnitsApproved\" ), SUM( a.\"ServiceUnitsDenied\"), SUM( a.\"ServiceUnitsPending\")
FROM \"Services\" a
WHERE a.\"ServiceDate\" >= ? AND a.\"ServiceDate\" <= ? AND a.\"ServiceReceived\" = ?" ;
The results are:
Count Approved Denied Pending
150 26 4 129
The Count is correct. However the values for Approved, Denied and Pending are based on all records and total 159.
Is there a way to set the query up so that I can get the correct sums for Approved, Denied and Pending?
In the interest of full disclosure, I used Seedcodes SQLExplorer to develop the initial query and then tweaked it to get Counts and Sums.
Currently to get the correct results, I resorted to standard FileMaker scripting, looped through the records and omit the duplicate IDs.