I'm executing the following SQL in with FileMaker's ExecuteSQL function:
SELECT "location", "account", SUM("amount")
FROM "TEMP"
GROUP BY "location", "account"
It's giving me very strange results.
LOC01,Account 1,-5.25
LOC01,Account 2,100811.57
LOC01,Account 3,13298.7299999999999942
LOC01,Account 4,17942.029999999999994
LOC01,Account 5,-12528.279999999999995
LOC01,Account 6,2048.9399999999999995
LOC01,Account 7,547.25
LOC01,Account 8,90295
LOC01,Account 9,-83
LOC01,Account 10,-340.11
LOC01,Account 11,-5619.3499999999999923
LOC01,Account 12,-780
LOC01,Account 13,-450
LOC01,Account 14,-98126.44
LOC01,Account 14
Account 15
Account 15
Account 16
Account 17
Account 17
Account 18,-540
LOC01, Account 14
Account 14
Account 14
Account 17
Account 14
Account 17,0
LOC01,Account 18,-869.95
LOC01,Account 19,-6100.01
LOC01,Account 20,-92849.6899999999999995
LOC01,Account 21,1150
LOC01,Account 22,-7801.4399999999999976
LOC02,Account 1,-4.25
Everything looks good until line 15. There we have the same location and account and no amount, then a few lines with only accounts, some duplicated, most without locations or amounts, some with just the location missing.
This pattern repeats for each location.
What could be causing this?
Because two of your records contain multiple values (separated by carriage returns) in the account field.