using sqlite3 TOTAL function
lazarus / 16-Databases

----------------------------------------------------------------------
davesimplewear · 2009-10-23 00:51

I am still trying to calculate totals in a sqlite3 database table, I
tried test db called sales with the following fields: Item
Varchar(30), SaleDate Date, Price Currency, Total Currency.

I enter Data and set a query :
Procedure Button1Click(Sender: TObject);
var
 SaleTotal:Currency;
begin
 TSales.DisableControls;// sqlite database using Table Sales
 TSales.Close;
 TSales.ExecQuery('SELECT TOTAL(Price) as Total from Sales');
 TSales.ExecQuery;
 TSales.Open;
 TSales.EnableControls;
 TSales.Edit;
 SaleTotal:= TSalesTotal.Value;
end;

This gives a value of 0.00

I tried the query in SQLITE Manager and it gave a result that was the
correct total of the Price fields but 0 in the Total field.

Where am I going wrong?
I would appreciate any help or an example.

Thanks in advance
David

----------------------------------------------------------------------
Nicola Gorlandi · 2009-10-23 11:12

Have you tried

Select Sum(Price) As Total from sales ?

----------------------------------------------------------------------
davesimplewear · 2009-10-23 22:06

Thank you for your reply.
Yes I Tried that, the sum function with sqlite3 returns integer only
and they have the total function which returns a float value.

----------------------------------------------------------------------
LuizAmérico · 2009-10-24 12:51

Remove the TSales.ExecQuery calls
Replace by  TSales.SQL := 'SELECT TOTAL(Price) as Total from Sales';

----------------------------------------------------------------------
davesimplewear · 2009-10-24 22:03

Thank you for your reply, I tried that but still no value, I have
created an extra table called Balance with one field called Bal :
value is Currency. I run the following query,
procedure TForm1.Button1Click(Sender: TObject);
begin
  TSales.DisableControls;
  TSales.ExecSQL('Insert into Balance select TOTAL(price) from
sales');
  TSales.ExecSQL;
  TSales.EnableControls;
  TBal.ApplyUpdates;
end; 
Bal shows the correct value but will not display in a dbEdit set with
the dataSource and table pointing to Balance.

----------------------------------------------------------------------
davesimplewear · 2009-10-26 07:20

Quoteprocedure TForm1.Button1Click(Sender: TObject);
begin
  TSales.DisableControls;
  TSales.ExecSQL('Insert into Balance select TOTAL(price) from
sales');
  TSales.ExecSQL;
  TSales.EnableControls;
  TBal.ApplyUpdates;
end;  OK I finally worked this out, I Created the table Balance with
two fields( BalID Integer Primary Key, Bal Currency)
I then run this query:
procedure TForm1.Button1Click(Sender: TObject);
begin
  TSales.DisableControls;
  TSales.ExecSQL('Insert into Balance (Bal) select TOTAL(price) from
sales');
  TSales.ExecSQL;
  TSales.EnableControls;
  TBal.ApplyUpdates;
end; 

It updates the BalID as autoinc and the bal field with the correct sum