AnsweredAssumed Answered

ExecuteSQL  DISTINCT?

Question asked by BruceHerbach on Jan 28, 2016
Latest reply on Jan 29, 2016 by BruceHerbach

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. 

Outcomes