asked on

SQL Query for performing calculations on summed Delphi DBGrid columns

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

PortletPaul

Why can't you just do a single query?

SELECT
      SUM(FullPrice)                 AS Sales
    , SUM(Cost)                      AS TotalCost
    , SUM(SalesTax)                  AS Tax
    , SUM(FullPrice) - SUM(Cost)     AS Profit
    , SUM(FullPrice) / SUM(Cost)     AS Margin
    , SUM(FullPrice) - SUM(SalesTax) AS CusReturn
FROM [TransactionEntry]
GROUP BY
      CAST(TransactionTime AS date)

Open in new window

But now I see something I cannot answer myself.

You have "Group By CAST(TransactionTime AS DATE)" in your code but you are not displaying CAST(TransactionTime AS DATE) as a column in the result.

Do you:
want to include the date as a column? or
not bother with this group by at all?

visionetv

ASKER

Hi;

The CAST DATE directive consolidates daily sales transaction results. I'll be displaying the date but that's not my immediate concern.

The problem is displaying the data retrieved from the [TransactionEntry] table into the proper DBGrid columns. The FullPrice AS Sales, Cost AS Cost, SalesTax AS Tax display properly. The SQL calculations in the Query are displayed in the Sales, Cost, and Tax fields rather than the Profit and Margin fields (Graphic attached).
SQL-Issue.JPG

visionetv

ASKER

Thank you for the Query re-draft but I'm still getting the same result as my original Query which is also a single Query. Please see the graphic in my previous post.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

visionetv

ASKER

Thanks for the UNION info and the SQL Query, I've accepted the solution.

Thanks again,
Visionetv

PortletPaul

:) no problem.

UNION is actually "wrong" if you are combining transactions. The job of UNION is to provide a "unique list". So for example if I had 2 tables of this data:

Table1.A
1.11
1.11
1.11
11.12

Table2.B
33.33
11.12

select table1.A from Table1
UNION
select Table2.B from Table2

would produce: just these 3 values

A
1.11
11.12
33.33
--------------------

I trust you can see that is wrong

-------------------
BUT:

select table1.A from Table1

UNION ALL

select Table2.B from Table2

will produce the full list

A
1.11
1.11
1.11
11.12
33.33
11.12

-----------

So, please be on guard if using UNION that in fact you really want a "unique list"

and also: UNION ALL is also faster (as it does not try to make a list unique)