Problems with SUM in SQL statement

Twins

Delphi Developer

Thu, 22 Mar 2001 03:00:00 GMT

Problems with SUM in SQL statement

I have to sum the values of the field SALARY and
store the result in a variable called "tot".
I wrote this code:

procedure SumRecords;
var tot:double;
begin
tot:=0;
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('SELECT NAME,ADDRESS,CITY,SALARY,');
Query1.SQL.Add('SUM (SALARY) as tot');
Query1.SQL.Add('FROM CUSTOMERS');
Query1.SQL.Add('GROUP BY NAME,ADDRESS,CITY,SALARY');
Query1.SQL.Add('ORDER BY NAME');
Query1.Open;
.....
end;

At the end of the procedure, the value of tot is always 0.
Can anyone tell me what's wrong?

Brian Bushay Tea

Delphi Developer

Fri, 23 Mar 2001 03:00:00 GMT

Re:Problems with SUM in SQL statement

Quote

>>procedure SumRecords;
>var tot:double;
>begin
> tot:=0;
> Query1.Close;
> Query1.SQL.Clear;
> Query1.SQL.Add('SELECT NAME,ADDRESS,CITY,SALARY,');
> Query1.SQL.Add('SUM (SALARY) as tot');
> Query1.SQL.Add('FROM CUSTOMERS');
> Query1.SQL.Add('GROUP BY NAME,ADDRESS,CITY,SALARY');
> Query1.SQL.Add('ORDER BY NAME');
> Query1.Open;
> .....
>end;
>At the end of the procedure, the value of tot is always 0.
>Can anyone tell me what's wrong?

You assign tot the value of 0 on the first line of your code an you do not
change it after that. The SQL isn't going to set the value of a variable and
your SQL is going to return multiple record so which one would it use?

--
Brian Bushay (TeamB)
Bbus...@DataGuidance.com

cezar nechifo

Delphi Developer

Fri, 23 Mar 2001 03:00:00 GMT

Re:Problems with SUM in SQL statement

Try to add the line >>> marked

Quote

Twins wrote:
> I have to sum the values of the field SALARY and
> store the result in a variable called "tot".
> I wrote this code:

> procedure SumRecords;
> var tot:double;
> begin
> tot:=0;
> Query1.Close;
> Query1.SQL.Clear;
> Query1.SQL.Add('SELECT NAME,ADDRESS,CITY,SALARY,');
> Query1.SQL.Add('SUM (SALARY) as tot');
> Query1.SQL.Add('FROM CUSTOMERS');
> Query1.SQL.Add('GROUP BY NAME,ADDRESS,CITY,SALARY');
> Query1.SQL.Add('ORDER BY NAME');
> Query1.Open;
>>>tot:=Query1.FieldByName('tot').AsInteger; >>>
> .....
> end;

> At the end of the procedure, the value of tot is always 0.
> Can anyone tell me what's wrong?

Roman Krejc

Delphi Developer

Fri, 23 Mar 2001 03:00:00 GMT

Re:Problems with SUM in SQL statement

Hi Twins, besides what others have written, did you really
meant to GROUP BY SALARY?
With this grouping, the group will be identified by
the unique combination of
NAME,ADDRESS,CITY _and_ SALARY fields,
so your tot column will be basically equal to salary column,
unless you have the same customer with the same salary
listed more than once in the table - then the tot column will return
thie value of salary column multiplied by the number of
rows where this particular NAME,ADDRESS,CITY,SALARY
combination occurs.
--
Roman
(please remove STOPSPAM. in header)
URL: www.rksolution.cz (Delphi corner)
MAIL: I...@rksolution.cz

Quote

Twins wrote in message <3617DC88.5BF83...@dimensione.com>...
>I have to sum the values of the field SALARY and
>store the result in a variable called "tot".
>I wrote this code:

>procedure SumRecords;
>var tot:double;
>begin
> tot:=0;
> Query1.Close;
> Query1.SQL.Clear;
> Query1.SQL.Add('SELECT NAME,ADDRESS,CITY,SALARY,');
> Query1.SQL.Add('SUM (SALARY) as tot');
> Query1.SQL.Add('FROM CUSTOMERS');
> Query1.SQL.Add('GROUP BY NAME,ADDRESS,CITY,SALARY');
> Query1.SQL.Add('ORDER BY NAME');
> Query1.Open;
> .....
>end;

>At the end of the procedure, the value of tot is always 0.
>Can anyone tell me what's wrong?

Other Threads

1. Problems with SUM in SQL statement

2. Problem with SQL statement sum in 32 bits ?

3. Wrong amount by using then SQL SUM-Statement

4. Problem w/SQL Insert statement, SQL Server nvarchar datatype, and colons :

5. SQL (Sum problems)

6. Problem with Local - SQL SUM() function

7. Problem with SQL-SUM function

8. SQL Query SUM problem ....

9. Using Sum in Insert Statement.

10. Problem with sql statement and query component