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 s.id ) [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:
~idList = Substitute ( SSN::FILTERIDS; "¶"; "','");
"SELECT SUM ( s.totAttendance )
FROM SSN AS s
JOIN PGM AS p ON s.id_PGM = p.id
JOIN PTI AS t ON p.id_PTI = t.id
WHERE s.id IN ('" & ~idList & "')
GROUP BY t.id" ;
~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.