Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 546
  • Last Modified:

Sum of calculated field

Howto get sum of a calculated field?
0
tscooter82
Asked:
tscooter82
  • 9
  • 6
1 Solution
 
esoftbgCommented:
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
 
ILECommented:

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
 
tscooter82Author Commented:
Is it possible to use sql for fields that are not in the table (only in the query fieldseditor)?
0
Industry Leaders: 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!

 
esoftbgCommented:
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
 
tscooter82Author Commented:
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
 
esoftbgCommented:
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
 
tscooter82Author Commented:
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
 
esoftbgCommented:
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
 
tscooter82Author Commented:
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
 
esoftbgCommented:
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
 
tscooter82Author Commented:
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
 
esoftbgCommented:
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
 
esoftbgCommented:
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
 
tscooter82Author Commented:
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
 
esoftbgCommented:
let's think about that:
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
 
esoftbgCommented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now