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 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:

 

"

Let (

[

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

~query =

"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 ; "" ; "" )

];

~result)

 

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.

 

Dave.

Outcomes