1 Reply Latest reply on Nov 1, 2016 2:11 PM by okramis

    A New SQL dilemma

    dsimonson

      Can anyone explain to me why the first expression works and the second one returns "?"?  All I added was the SUM() statement around one of the fields.  I know it works, what did I do wrong"

       

      First Expression:

      ExecuteSQL (

      "SELECT a.med_amt, b.med_name_short,  b.med_units

      FROM Obs_Meds a

      INNER JOIN Medications b ON a.fk_med_id = b.idMedication

      ORDER BY b.med_name_short ASC";

      "";"")

       

      Returns:

      12,Bup5,ml

      50,Fent,mcg

      30,KTR,mg

      10,Lido,ml

      ...

       

      Second Expression:

      ExecuteSQL (

      "SELECT SUM(a.med_amt), b.med_name_short,  b.med_units

      FROM Obs_Meds a

      INNER JOIN Medications b ON a.idMedication = b.idMedication

      ORDER BY b.med_name_short ASC";

      "";"")

      Returns: "?"

       

      Sheesh.

        • 1. Re: A New SQL dilemma
          okramis

          If you use aggregates like SUM(), COUNT(), AVG()...., you need to add a GROUP BY clause with all non aggregate fields like this:

           

          ExecuteSQL (

          "SELECT SUM(a.med_amt), b.med_name_short,  b.med_units

          FROM Obs_Meds a

          INNER JOIN Medications b ON a.idMedication = b.idMedication

          GROUP BY b.med_name_short, b.med_units

          ORDER BY b.med_name_short ASC";

          "";"" )

           

          Regards

          Otmar

          1 of 1 people found this helpful