SQL Query for performing calculations on summed Delphi DBGrid columns
Posted on 2014-08-18
I need some help in fixing an ADO SQL Query in Delphi for populating a DBGrid. The DB server is SQL 2008/R2.
The statement is:
SELECT SUM(FullPrice) AS Sales, SUM(Cost) AS TotalCost, SUM(SalesTax) AS Tax FROM [TransactionEntry] Group By CAST(TransactionTime AS DATE)
SELECT SUM(FullPrice-Cost) AS Profit, SUM(FullPrice/Cost) AS Margin, SUM (FullPrice-SalesTax) AS CusReturn FROM [TransactionEntry] Group By CAST(TransactionTime AS DATE)
The section before the UNION populates 3 of the 6 DBGrid Columns, the remaining section however places the query results in the same 3 columns as those in the first section rather than the last 3 columns where they are supposed to belong.
Also (Cost/FullCost) without the SUM creates a compiler error. I realize using the SUM directive will not give me a correct result but it’s the only way right now to keep the compiler from complaining as is Column 6 which is there to satisfy the UNION directive.
The goal is to subtract the 'FullPrice' DBGrid Column from the Cost column and place the result in the Profit column and the division of Cost/FullCost columns in the Margin column.
Any help would be greatly appreciated as I spent the weekend trying to solve the issue.