We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

MySQL query help

DigitalNam
DigitalNam asked
on
Medium Priority
403 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

Comment
Watch Question

CERTIFIED EXPERT

Commented:
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
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Author

Commented:
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

CERTIFIED EXPERT

Commented:
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
   

Author

Commented:
pratima_mcs: I need to use the WHERE clause to show only certain clients.
CERTIFIED EXPERT

Commented:
then you will get only one row

can you give some data and what exactly you need tin result ?
CERTIFIED EXPERT
Top Expert 2011

Commented:
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

Author

Commented:
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
CERTIFIED EXPERT

Commented:
means you want to show only one record

client1 - sum

Author

Commented:
pratima_mcs: No I want to show the running total in a Label with all the records for Client1 in a grid.
CERTIFIED EXPERT
Top Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
This image shows what I am looking for. Screenshot
CERTIFIED EXPERT
Top Expert 2011

Commented:
And what happens when you execute the code I provided?

Author

Commented:
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

CERTIFIED EXPERT
Top Expert 2011

Commented:
Cheers
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.