SQL Sum with INNER JOIN and WHERE
lazarus / 16-Databases

----------------------------------------------------------------------
bobonwhidbey · 2015-01-31 18:34

Two tables:
Cust: ID, State
Order: ID, Type, Amt

I'm tying to get the sum of Amt when TYPE="Fin" and State="WA"

Here's what I've tried:
Code: [Select]    SQL := 'SELECT *, TOTAL(Order.Amt) AS SC '
      + ' FROM Order INNER JOIN Cust ON Order.ID=Cust.ID'
      + ' WHERE Order.Type="Fin" AND Cust.State ="WA"';
    tot := sltb.FieldIndex['SC'];
Tot always comes up with the number of records in the CUST file?

Please help me figure this out.

----------------------------------------------------------------------
sfeinst · 2015-01-31 19:25

I would probably use SQL like:

SELECT Sum(Order.Amt) AS SC
FROM Order INNER JOIN Cust ON Order.ID=Cust.ID
WHERE Order.Type='Fin' AND Cust.State ='WA'

----------------------------------------------------------------------
A.S. · 2015-01-31 19:55

Look carefully what you wrote:
  tot := sltb.FieldIndex['SC'];
"FieldIndex" returns the index of 'SC' field in dataset.
Probably you should retrieve data by the following statement:
  tot := sltb.FieldByName('SC').AsInteger

----------------------------------------------------------------------
bobonwhidbey · 2015-01-31 21:09

I tried
Code: [Select]    SQL := 'SELECT Sum(Order.Amt) AS SC '+
      ' FROM Order INNER JOIN Cust ON Order.ID=Cust.ID'
      ' WHERE Order.Type='Fin' AND Cust.State ='WA';
    sltb := slDb.GetTable(SQL);
    s := sltb.FieldBYName['SC'];

No success     s was blank

----------------------------------------------------------------------
rvk · 2015-01-31 21:25

Did you read the second answer? The one from A.S. where he caught the
error (that you forgot the .AsInteger). And it was quite a good catch
too

----------------------------------------------------------------------
bobonwhidbey · 2015-02-01 17:20

My problem was - the WHERE clause is CASE SENSITIVE.

Since the data isn't consistently upper or lower case, I think a good
solution is:

WHERE Upper(Order.Type)='FIN'

this works.