Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

SQL Query for performing calculations on summed Delphi DBGrid columns

Avatar of visionetv
visionetvFlag for United States of America asked on
Programming Languages-OtherMicrosoft SQL ServerSQL
6 Comments1 Solution1410 ViewsLast Modified:
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)
UNION
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.

Thank you,
Visionetv