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
visionetvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
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?
0
visionetvAuthor Commented:
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
0
visionetvAuthor Commented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

PortletPaulfreelancerCommented:
Yes, I get it.

UNION is absolutely the wrong technique for your needs (in fact it could lead to errors)

UNION or UNION ALL is will create new rows, it does NOT create new columns  (ever)
----------------

which is why I have not proposed it

----------------

Your issue is relatively straight forward.

You need to treat "SUM(some-field-here)" as the field now, and so if you need arithmetic involving several summed values all of them need that treatment.

Like this
    , SUM(FullPrice) - SUM(Cost)     AS Profit

(the sum of price) minus (the sum of cost)

------------------------

My previous post contains a single select query with the required columns. You can adjust that for how you want to handle the date, probably something like this:
SELECT
      CAST(TransactionTime AS date)  AS TransDate
    , 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
visionetvAuthor Commented:
Thanks for the UNION info and the SQL Query, I've accepted the solution.

Thanks again,
Visionetv
0
PortletPaulfreelancerCommented:
:) 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)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.