1 2 3 Previous Next 42 Replies Latest reply on Jul 6, 2015 1:59 AM by davehob

    eSQL - need a DISTINCT, but not sure where?

    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.

        1 2 3 Previous Next