Recent

Author Topic: SQL Sum with INNER JOIN and WHERE  (Read 4457 times)

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
SQL Sum with INNER JOIN and WHERE
« on: January 31, 2015, 07:34:22 pm »
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.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

sfeinst

  • Full Member
  • ***
  • Posts: 230
Re: SQL Sum with INNER JOIN and WHERE
« Reply #1 on: January 31, 2015, 08:25:53 pm »
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.

  • Jr. Member
  • **
  • Posts: 76
Re: SQL Sum with INNER JOIN and WHERE
« Reply #2 on: January 31, 2015, 08:55:30 pm »
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

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: SQL Sum with INNER JOIN and WHERE
« Reply #3 on: January 31, 2015, 10:09:40 pm »
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
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

rvk

  • Hero Member
  • *****
  • Posts: 6112
Re: SQL Sum with INNER JOIN and WHERE
« Reply #4 on: January 31, 2015, 10:25:05 pm »
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  :D

bobonwhidbey

  • Hero Member
  • *****
  • Posts: 592
    • Double Dummy Solver - free download
Re: SQL Sum with INNER JOIN and WHERE
« Reply #5 on: February 01, 2015, 06:20:52 pm »
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.
Lazarus 3.0RC2, FPC 3.2.2 x86_64-win64-win32/win64

 

TinyPortal © 2005-2018