SUM and SQL

[PJB]

How come this gives me the right sum

sqlstring = "Select SUM([GEService].[PayAmount]) AS [GEAmount] From GEService"

But this

sqlstring = "Select SUM([GEService].[PayAmount]) AS [GEAmount], SUM([WSService].[PayAmount]) AS [WSAmount] From GEService, WSService"

returns the 2 seperate amounts the way i'd like it to but the are exactly 3X and 4X (respectively) the amount they should be?

[PJB]

...and if i add more to the string(there's 5 tables all together that i need to return sums on) the number keeps getting larger????

[HunterMcCray]

Originally posted by PJB
...and if i add more to the string(there's 5 tables all together that i need to return sums on) the number keeps getting larger????

I rarely use SQL Sum statements because I have run into similar problems, and have found that most of the time I can Sum the fields that I want easier in coded loops; but I think that the answer lies in the way the Query engine works. I am guessing here, but I think that what is happening is that because you are Selecting Two Tables without any Joins that you are creating result Fields for every occurance of any record....

Table1 Key Table2 Key Table1 Value Table2 Value
1 1 17 15
1 1 17 15
2 2 20 32
2 2 20 32
.
.
.
IF you had three tables then there would be three occurances, four tables then four occurances....SQL behaves by a set of rules that allow you to do things that one might question "Why?". If there is no relationship between the tables then it will fill in values for all of the fields for each valid record regardless of how silly it seems to us.
I would suggest that instead of having tables for each vendor or customer that you create one table that holds all of the data and has a VendorId field or CustomerId field, this will greatly simplify your queries, and make adding new accounts much easier. Then you can implement the "BY" key word and your Sum recordset will do exactly what you are describing.

Hope This Helps,

Hunter

[PJB]

don't know if one table would work in this case, what are the possibilities of putting the records into a temp. table at runtime and then extracting the SUMs?

[HunterMcCray]

Originally posted by PJB
don't know if one table would work in this case, what are the possibilities of putting the records into a temp. table at runtime and then extracting the SUMs?

Why not just build a temp table and use code to populate the temp table with the totals? I do this with several reports where SQL is slow, or SQL does not want to total, group, and sum the way I would like. In fact in a database I use a great deal I have a table entitled "Totals" that has several generic ID fields, a date field, one field I call "Typ" and then twenty fields called Cur1, Cur2 etc. I use the "Typ" field to assign a value to the Type of data that the cur Fields contain. When displaying Sales Totals for a user input date range, for example, I assign Typ=20 in the where clause. When totaling a particular item's sales history for a user defined item and date range I use negative ItemNum. I am sure that you get the idea. I can hold totals for a large number of temp totals that are kept in memory until the next time a user looks for that particular total type then I simply use a delete query to delete all records of that Typ before I begin re-total. In several complex Totaling cases I have found that totaling through code can be orders of magnitudes faster than its SQL counter part. (In one example the SQL Query of 135,000 records took close to an hour where the coded counter part took a few moments!) Before you get too excited there are a number of cases where the SQL statments were much faster than the Code. When you are dealing with a large number of records it often pays to try several different methods.

Hope it helps,

Hunter