I'm tring to use the SQL statement below to generate a margin percentage but as I understand it, FM SQL doesn't allow you to query on aliased columns? In this case, COST and INC are fields (columns) which are generated using a CASE statement, with the source field being called AMOUNT. I cannot return any data using the query below, but if I simply change it to Select SUM(Amount) I get the grand total of the column. I don't want that, just a couple of sub totals.
CUST = Customer_Home::Cust_PK;
SQL = "Select (SUM(INC)-SUM(COST))/SUM(INC) from DataTable where