[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MySQL query help

Posted on 2011-04-26
15
Medium Priority
?
381 Views
Last Modified: 2012-05-11
Hope that someone can help me out on this one.
I have a query and I need to sum the VAT_Amount field and show it in a Label. The code I have shows me the sum but I need to show all the records from Client_Name as well. When I use this code it only shows me one row with the total.



with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := ('SELECT * FROM  input_vat WHERE Client_Name = :Client');
//    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    Open;

  end;

Open in new window

0
Comment
Question by:DigitalNam
  • 6
  • 5
  • 4
15 Comments
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35465516
are you saying
SELECT * FROM  input_vat WHERE Client_Name = :Client
this query retuns more records
while
SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client
returns only one rerod where you need multiple records

then there must be some other field on which you need to do this some say col1
then change query like this

SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client
Group by col1
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35465522
You can retrieve the sum and show it in the label and then execute another query to select the required records:
with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;
    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    Label1.Caption := FieldByName('tmpTotal').AsString;
    Active := False;
    SQL.Text := ('SELECT * FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    //Open; // you already activated that
  end;

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35465577
JimyX: I can't get the value in a label. It only shows it in the grid.

pratima_mcs: I tried using to group by Client_Name but it only returns one row
with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;
//    SQL.Text := ('SELECT * FROM  input_vat WHERE Client_Name = :Client');
    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client GROUP BY Client_name');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    Open;
   end;
     Label4.Caption := FloatToStr(DM.IV_TabletmpTotal.Value);
end;

Open in new window

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35465598
if you want to give group by client name do not give it in where


  SQL.Text := ('SELECT Client_name',SUM(VAT_Amount) AS tmpTotal FROM input_vat  GROUP BY Client_name');


try the query in query anlyser
   
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35465604
pratima_mcs: I need to use the WHERE clause to show only certain clients.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35465608
then you will get only one row

can you give some data and what exactly you need tin result ?
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35465623
You can dis-attach the DataSource of the iv_query so the result of the first query will not appear yet until you execute the second query:
with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;
    DataSource := nil;
    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    if not IsEmpty then
      Label1.Caption := FieldByName('tmpTotal').AsString
    else
      Label1.Caption := 'No Vat for the client: '+tmpClientName;
    Active := False;
    DataSource := DataSource1;
    SQL.Text := ('SELECT * FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
  end;

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35465624
pratima_mcs: I have the following fields in my table:

Client_Name
Inv_Total
Vat_Amount

so I need to show all the clients eg: where only Client_name = Client 1 with a label summing the VAT amount of all records for Client 1
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 35465646
means you want to show only one record

client1 - sum
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35465653
pratima_mcs: No I want to show the running total in a Label with all the records for Client1 in a grid.
0
 
LVL 24

Accepted Solution

by:
jimyX earned 2000 total points
ID: 35465675
Did you try my last post, here is updated:
with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;
    DataSource := nil;
    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    if not IsEmpty then
      Label1.Caption := IntToStr(FieldByName('tmpTotal').AsInteger);
    else
      Label1.Caption := 'No Vat for the client: '+ tmpClientName;
    Active := False;
    DataSource := DM.DataSource1;
    SQL.Text := ('SELECT * FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
  end;

Open in new window

0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35465702
This image shows what I am looking for. Screenshot
0
 
LVL 24

Expert Comment

by:jimyX
ID: 35465716
And what happens when you execute the code I provided?
0
 
LVL 1

Author Comment

by:DigitalNam
ID: 35465723
JimyX: great stuff man. Thank you. I needed to make a few changes but it works.
with DM.iv_query do
  begin
    Active := False;
    SQL.Clear;
   // DataSource := nil;
    SQL.Text := ('SELECT SUM(VAT_Amount) AS tmpTotal FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
    if not IsEmpty then
      Label4.Caption := FloatToStr(FieldByName('tmpTotal').AsFloat)

    else
      Label4.Caption := 'No Vat for the client: '+ tmpClientName;
    Active := False;
   // DataSource := DM.IV_Source;
    SQL.Text := ('SELECT * FROM input_vat WHERE Client_Name = :Client');
    ParamByName('Client').Value := tmpClientName;
    Active := True;
  end;

Open in new window

0
 
LVL 24

Expert Comment

by:jimyX
ID: 35465743
Cheers
0

Featured Post

Independent Software Vendors: 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!

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…
Have you ever had your Delphi form/application just hanging while waiting for data to load? This is the article to read if you want to learn some things about adding threads for data loading in the background. First, I'll setup a general applica…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

867 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