?
Solved

SQL Query for performing calculations on summed Delphi DBGrid columns

Posted on 2014-08-18
6
Medium Priority
?
1,041 Views
Last Modified: 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)
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
0
Comment
Question by:visionetv
  • 3
  • 3
6 Comments
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40269293
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
 

Author Comment

by:visionetv
ID: 40269362
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
 

Author Comment

by:visionetv
ID: 40269370
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 49

Accepted Solution

by:
PortletPaul earned 2000 total points
ID: 40269376
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
 

Author Closing Comment

by:visionetv
ID: 40269431
Thanks for the UNION info and the SQL Query, I've accepted the solution.

Thanks again,
Visionetv
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40269439
:) 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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Article by: evilrix
Looking for a way to avoid searching through large data sets for data that doesn't exist? A Bloom Filter might be what you need. This data structure is a probabilistic filter that allows you to avoid unnecessary searches when you know the data defin…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
The goal of the video will be to teach the user the concept of local variables and scope. An example of a locally defined variable will be given as well as an explanation of what scope is in C++. The local variable and concept of scope will be relat…
Suggested Courses
Course of the Month15 days, 3 hours left to enroll

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question