AnsweredAssumed Answered

eSQL - need a DISTINCT, but not sure where?

Question asked by davehob on Jul 1, 2015
Latest reply on Jul 6, 2015 by davehob

A while ago, I had a problem with an eSQL query, in which I was counting records by doing a COUNT of the primary key.  People (Beverly and Nicolai) pointed out that, to prevent records from being counted twice, I needed a DISTINCT, as in "SELECT COUNT ( DISTINCT ) [etc.].


I now have a similar problem, and, given that the result returned is consistently twice what it should be, I feel confident that the remedy will be similar, and feel rather stupid for not being able to get it!


This time I'm after a SUM of a number field, and I don't know where I need the DISTINCT bit.


The structure is PTI > PGM > SSN (a Programme Title (PTI) has many Programmes (PGM), which has many Sessions (SSN)), and I need the total of the totAttendance field for each PTI.  (I'll also need it later for each PGM within the PTI, but I'll cross that bridge when I get to it.)


Here's what I've got so far for the query:



Let (


~idList = Substitute ( SSN::FILTERIDS; "¶"; "','");

~query =

"SELECT SUM ( s.totAttendance )


   JOIN PGM AS p ON s.id_PGM =

   JOIN PTI AS t ON p.id_PTI =

WHERE IN ('" & ~idList & "')


~result = ExecuteSQL( ~query ; "" ; "" )




As I said above, the result is consistently doubled.  (Obviously I don't want DISTINCT values of totAttendance.)


Any pointers would be very gratefully received.