• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 407
  • Last Modified:

Query on multiple tables on multiple fields

Could someome please point me in the right direction. I am using DXE and Mydac to connect to a MySQL database. I am very new to MySQL.

I have 3 tables and in those tables I have 3 fields that I need to sum and place those values in variables to send to my report.

I appreciate any help.
  • 2
2 Solutions
You can use SQL query, such as:

Select Sum(T1.FieldA) Total from Table1 T1

Select Sum(T2.FieldB) Total from Table2 T2

Select Sum(T3.FieldC) Total from Table3 T3

And then use DBQuery.FieldByName('Total').AsFloat (or AsInteger) to assign the total into the variables.
Drop TMyQuery and connect it with TMyConnection and use the following example

procedure TForm1.Button1Click(Sender: TObject);
var MyField: string;
    Sum: real;
MyField := 'sum(Field1)+sum(Field2)+sum(Field3)'; // Fields of the Table1
MyQuery1.SQL.Text := 'select '+MyField+' from table1';
Sum := MyQuery1.FieldByName(MyField).AsFloat;
MyField := 'sum(Field1)+sum(Field2)+sum(Field3)'; // Fields of the  Table2
MyQuery1.SQL.Text := 'select '+MyField+' from table2';
Sum := MyQuery1.FieldByName(MyField).AsFloat + sum;
MyField := 'sum(Field1)+sum(Field2)+sum(Field3)'; // Fields of the Table3
MyQuery1.SQL.Text := 'select '+MyField+' from table3';
Sum := MyQuery1.FieldByName(MyField).AsFloat + sum;

showmessage('Total is '+FloattoStr(Sum));

Open in new window

DigitalNamAuthor Commented:
Thank you for the replies. Something came up and I will only be able to test the code tomorrow. Will keep you updated.
DigitalNamAuthor Commented:
thank you, you guys put me on the correct way to achieve this.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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