Avatar of Phreonx
Phreonx
 asked on

DBGrid, ADOQuery, MSSQL: Row Sum Revisited

Hello all,
I'm fetching a set of a data from an SQL Server database into a DBGrid using, an ADOConnection, an ADOQuery and a plain Datasource. Eight sample rows in the DBGrid look like this:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt
00000001          TGK756                11/9/06           44.4    50
00000001          TGK755                10/9/06           43.3    60
00000001          TGK755                10/9/06           43.3    30
00000002          TGK754                9/9/06             42.2    40
00000002          TGK753                8/9/06             41.1    20
00000003          TGK755                10/9/06           43.3    80
00000004          TGK755                10/9/06           43.3    50
00000004          TGK755                10/9/06           43.3    30

I have implemented a solution that right now gives me the following (expected) results:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
00000001          TGK756                11/9/06           44.4    50    140 (30+50+60)
00000001          TGK755                10/9/06           43.3    60    140 (30+50+60)
00000001          TGK755                10/9/06           43.3    30    140 (50+60+30)
00000002          TGK754                9/9/06             42.2    40    200 (140+40+20) 140 is the previous sum
00000002          TGK753                8/9/06             41.1    20    200 (140+40+20)
00000003          TGK755                10/9/06           43.3    80     280 (200+80)
00000004          TGK755                10/9/06           43.3    50     360 (280+50+30)
00000004          TGK755                10/9/06           43.3    30     360 (280+50+30)

I would like however, to include a dynamic field to SUM the Qnt but in the following fashion:

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
00000001          TGK756                11/9/06           44.4    50    50
00000001          TGK755                10/9/06           43.3    60    50+60
00000001          TGK755                10/9/06           43.3    30    50+60+30
00000002          TGK754                9/9/06             42.2    40    40
00000002          TGK753                8/9/06             41.1    20    40+20
00000003          TGK755                10/9/06           43.3    80     80
00000004          TGK755                10/9/06           43.3    50     50
00000004          TGK755                10/9/06           43.3    30     50+30

And so on for all the rows returned by the ADOQuery. Please notice that the Aggregate_SUM field must represent the SUM of the rows with RowNumber <= CurrentRowNumber *FOR *EACH Cus_Code as shown in the above diagram. All help is welcome.

Thank you in advance
Delphi

Avatar of undefined
Last Comment
Mike Littlewood

8/22/2022 - Mon
Mike Littlewood

The problem is that there is nothing unique to differentiate between rows for each custID
For example
00000004          TGK755                10/9/06           43.3    50     50
00000004          TGK755                10/9/06           43.3    30     50+30

Now if there was a unique ID per row (which really you should have),
Say it looked like this
ID      Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
1      00000004          TGK755                10/9/06           43.3    50     50
2      00000004          TGK755                10/9/06           43.3    30     50+30

You would be able to do a nested select that would look something like
( SELECT SUM( Qnt ) FROM yourtable WHERE Cus_Code = C.Cus_Code AND ID >= C.ID ) AS Aggregate_SUM
Mike Littlewood

Forgot to say the table C would be your outside table (should be the same one as in the nested select).
For example

SELECT ID, Cus_Code, Transaction_Kind, Transaction_Date, Price, Qnt,
  ( SELECT SUM( Qnt ) FROM yourtable WHERE Cus_Code = C.Cus_Code AND ID >= C.ID ) AS Aggregate_SUM
FROM
  yourtable C
WHERE
..................
Mike Littlewood

If you wanted to go back to something along the lines of your other question

https://www.experts-exchange.com/Programming/Programming_Languages/Delphi/Q_21985877.html

You could again hold a local variable and just reset it on change of Cus_Code and start to sum the quantity field again.

I prefer doing it in a query and letting the server do the work, just make sure you have your main WHERE clause fields indexed if you can.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Phreonx

ASKER
Mike:
"If you wanted to go back to something along the lines of your other question"
yes that is exactly what Im looking for. However, the requirements changed dramatically since yesterday and Im recoding a major part of my initial code. That's why the calculation formula of the Aggregate_Sum Field changed.

"You could again hold a local variable and just reset it on change of Cus_Code and start to sum the quantity field again."
I tried implementing this solution this morning but unfortunately got caught up in several other activities and could not finish it.

I'll first try a nested select as you suggested otherwise Im going to need your help regarding the second approach.

Thanks
Phreonx

ASKER
No luck with the nested SELECTs
Mike Littlewood

How does your main query behind

Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
00000001          TGK756                11/9/06           44.4    50    50
00000001          TGK755                10/9/06           43.3    60    50+60
00000001          TGK755                10/9/06           43.3    30    50+60+30
00000002          TGK754                9/9/06             42.2    40    40
00000002          TGK753                8/9/06             41.1    20    40+20
00000003          TGK755                10/9/06           43.3    80     80
00000004          TGK755                10/9/06           43.3    50     50
00000004          TGK755                10/9/06           43.3    30     50+30

know what order it is to be displayed
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Phreonx

ASKER
If you mean the ORDER BY clause, it is ORDER BY Cus_Code ASC
Mike Littlewood

Well the first issue I can see is how does it know which of these 2 would be displayed first

00000004          TGK755                10/9/06           43.3    50    
00000004          TGK755                10/9/06           43.3    30    

If the SQL just chooses at random, then it is impossible to really work out in which order to SUM the quantities.

A Nested select should do the job if it has the correct order.
Is the query the same as the one you posted on the last question?
Phreonx

ASKER
"Well the first issue I can see is how does it know which of these 2 would be displayed first"

Well, I don't see any apparent way to distinguish the order between the two and this is due to the way the database table was initially created.

"Is the query the same as the one you posted on the last question?"
No. If we manage though, using the query from my previous question, to have the required data returned, I can revert with no great hassle
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Phreonx

ASKER
Ok, there's a field I could use in order to discriminate between these two. It's called aa:

AA    Cus_Code   Transaction_Kind   Transaction_Date   Price   Qnt   Aggregate_SUM
99     00000004          TGK755                10/9/06           43.3    50    
98     00000004          TGK755                10/9/06           43.3    30    

Hope that helps
ASKER CERTIFIED SOLUTION
Mike Littlewood

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Mike Littlewood

oops just noticed you want the sum at the bottom
just need to change the date check around

select Cus_Code, Transaction_Kind, Transaction_Date, Price, Quantity,
      (select sum(Quantity) from miketemp where Cus_Code = C.Cus_Code and Transaction_Date >= C.Transaction_Date)

from miketemp C

order by Cus_Code, Transaction_Date DESC


Just play around with the order of Transaction_Date and the Transaction_Date >= C.Transaction_Date until you get the correct order you are looking for (obvioously only 4 options)
sun4sunday

If you can create a local table, you can manuplate the data and pump the data into the local table and show this to the grid.

>>I would like however, to include a dynamic field to SUM the Qnt but in the following fashion:
Fetch data from server  order by for Cus_Code

Then create a temp table.

Check the Cust_Code changes or not through delphi,
if not insert the record into temp table. Hold the required value in a variable(aggregare sum)
go to next record, check for the Cust_Code changes or not. If not changed add the required value into the variable and insert that record into the temp table.
Follow until the end of the records.

Connect the temp table to the grid.

This can be done only if the you are allowed to create and pump data into the local table. All depends on your situations.

sun4sunday
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Phreonx

ASKER
Mike, I tried your solution on Northwind after I modified the original table to include some redundancy and I'm indeed getting the required dataset returned. I'll get back to you tomorrow and hopefully, after the required adaptations, your solution will do the trick. Thanks for your time
Phreonx

ASKER
Works great Mike. Is there a way to filter the results first, based on Transaction_Kind (eg Where Transaction_Kind LIKE LGA%) and then apply the calculation to get the same results ?
Mike Littlewood

You should be able to put in the WHERE clause

Using the last set of data I put in

where Transaction_Kind LIKE 'TGK755%'

and I got these results

00000001      TGK755      2006-09-10 10:00:00.000      43.30      60      110
00000001      TGK755      2006-09-10 00:00:00.000      43.30      30      140
00000003      TGK755      2006-09-10 00:00:00.000      43.30      80      80
00000004      TGK755      2006-09-10 10:00:00.000      43.30      50      50
00000004      TGK755      2006-09-10 00:00:00.000      43.30      30      80
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck