PSI

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

Discussion created by PSI on Sep 16, 2013
Latest reply on Sep 17, 2013 by 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

Outcomes