4 Replies Latest reply on Sep 17, 2013 6:03 AM by PSI

    ExecuteSQL - Select State, Sum(GT) Order by fails

    PSI

      I am trying to get Total Non Taxable sales for a give date range. I have searched but can't find an answer.

       

      This is what I am trying to achieve. x = Sum ( GrandTotal )

      NY, xxx.xx

      NJ, xxxx.xx

       

      ExecuteSQL (

      "SELECT ST_State, Sum ( GrandTotal )

      FROM Orders

      WHERE zF_Invoice=1 and zF_ME_Order=0 and zF_OPCL=0 and TaxExempt=0 and zF_NonTaxable=0 and OrderDate>=? and OrderDate<=?

      ORDER BY ST_State " ; "" ; "" ; UIReports::gReportDateStart ; UIReports::gReportDateEnd )

       

      If I remove Sum() and Order By works but it gives me separate values for each order in each state.

       

      I have also tried..still no go.

      ExecuteSQL (

      "SELECT ST_State, Sum ( GrandTotal ) AS gt_amt

      FROM Orders

      WHERE zF_Invoice=1 and zF_ME_Order=0 and zF_OPCL=0 and TaxExempt=0 and zF_NonTaxable=0 and OrderDate&gt;=? and OrderDate<=?

      GROUP BY ST_State

      ORDER BY gt_amt " ; "" ; "" ; UIReports::gReportDateStart ; UIReports::gReportDateEnd )

       

      There must be a way?

       

      Thanks,

      John Morina