Solved

# Sum of calculated field

Posted on 2003-03-04
Medium Priority
542 Views
Howto get sum of a calculated field?
0
Question by:tscooter82
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 9
• 6

LVL 12

Expert Comment

ID: 8067292
Example: // Fields:  PRICE, QUANTITY, MONEY
// MONEY := PRICE * QUANTITY

function  GetSumCalcFields: Double;
var
D:      Double;
begin
D := 0;
try
Query.Last;
while not Query.BOF do
begin
D := D + (Query.FieldByName('PRICE').AsFloat
* Query.FieldByName('QUANTITY').AsFloat);
Query.Prior;
end;
Query.First;
finally
Result := D;
end;
end;
.......................................................
There is another way by using SQL:
SELECT SUM(PRICE*QUANTITY) AS MONEY FROM TRADE
0

LVL 3

Expert Comment

ID: 8067907

that not work with local bases onli with sql bases

so the solution is for local is

function sum:double;
var r:double;
begin
r:=0;
query1.first;
while not query1.eof do
begin
r:=r+query1.fieldbyname('calcfieldname').asfloat;
query1.next;
end;
sum:=r;
end;

0

Author Comment

ID: 8073502
Is it possible to use sql for fields that are not in the table (only in the query fieldseditor)?
0

LVL 12

Expert Comment

ID: 8075466
Yes it is possible to use SQL for generating fields like MONEY and FOR_PAY that are not in the table:
SELECT SUM(PRICE*QUANTITY) AS MONEY FROM TRADE
or
SELECT PRICE, QUANTITY, PRICE*QUANTITY AS FOR_PAY FROM TRADE
0

Author Comment

ID: 8081300
I didnt mean that, i meant:
i have a table which contains 2 columns price,quantity. in the querys field editor i made an field that is called taxes.

my question is: is it possible to use sql like? select taxes from query1?????
0

LVL 12

Expert Comment

ID: 8087614
if you know the tax-rate (may be 16%) example:

procedure Form1.Proc;
var
S:   string;
T:   string;
begin
T := 'SELECT PRICE, QUANTITY, PRICE*QUANTITY*0.16 AS TAX FROM TRADE GROUP BY PRICE, QUANTITY';
Query1.Close;
Query1.SQL.Text := T;
Query1.Prepare;
Query1.Open;
S := Query1.FieldByName('TAX').AsString;
ShowMessage(S);
end;
0

Author Comment

ID: 8087658
yes but what is TRADE!! isnt it a database? my question is again: is it possible to use sql on a query which doesnt hava a database connected to it, only fields made in the fields editor!!
0

LVL 12

Expert Comment

ID: 8087670
it is impossible to use sql like: 'select taxes from query1' because query1 is Delphi component wich can read data from real table from real database.
above example is right way to understand how in the Query1 will appear 3 fields after its Open:
1. Query1.FieldByName('PRICE')
2. Query1.FieldByName('QUANTITY')
3. Query1.FieldByName('TAX')
0

Author Comment

ID: 8087682
yes. but i have 2 querys. i wantto connect query2 to query1 which contains fields

1. Query1.FieldByName('PRICE')
2. Query1.FieldByName('QUANTITY')
3. Query1.FieldByName('TAX')
0

LVL 12

Expert Comment

ID: 8087687
it is impossible to use sql on a query which is not connected to a database.
TRADE is a table into DATABASE. TRADE contains two fields: PRICE and QUANTITY.
Query1 reads PRICE and QUANTITY and calculate TAX.
That's all
0

Author Comment

ID: 8087701
yes but i want another query to calculate the taxes of all the records. or is it possible to have 2 sql statements in the sql texteditor at the object inspector??
0

LVL 12

Expert Comment

ID: 8087705
what is that "connect query2 to query1" ?
you said that you need to 3 fields from table which contains 2 fields.
my example shows how you get 3 fields into Query1.
i think you don't need Query2 at all !
0

LVL 12

Expert Comment

ID: 8087732
Query1 does all you want for all records in the table. Imagine that below are the results from executing of Query1. They will be:
PRICE      QUANTITY       TAX
2             5       1.6
5             4       3.2
.............................
40           2.5      16.0
0

Author Comment

ID: 8087733
i have a query which calculates the sum price*quantity of all record and puts it into a field total that isnt a "real" table field. then i wanto count the tax for that field "total" and put it into another "not a table" field. is that possible?
0

LVL 12

Expert Comment

ID: 8087810
you need a new field only for "total".
it is possible, but i i think it is not good solution,
i will show how it will seems:

PRICE      QUANTITY       TAX    TOTAL TAX
2             5       1.6          20.8
5             4       3.2          20.8
...........................................
40           2.5      16.0          20.8

is that you need ???????
0

LVL 12

Accepted Solution

esoftbg earned 50 total points
ID: 8087837
oh, i understand, you need Query2 only for SUM of the fields. Yes this is possible:
procedure Form1.Proc2;
var
S:   string;
T:   string;
begin
T := 'SELECT SUM(PRICE) AS SUM_PRICE, SUM(QUANTITY) AS SUM QUANTITY, SUM(PRICE*QUANTITY*0.16) AS SUM_TAX FROM TRADE';
Query2.Close;
Query2.SQL.Text := T;
Query2.Prepare;
Query2.Open;
end;
0

## Featured Post

Question has a verified solution.

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

In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, thâ€¦
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have toâ€¦
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to fâ€¦
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can reâ€¦
###### Suggested Courses
Course of the Month13 days, 21 hours left to enroll