SUM() Problem in Paradox Database

Question

Hello All,

I'm currently working with a paradox database that was implemented before I started working at my current job at an insurance firm.

Long story short is that when I am trying to to compile a query of the debit/credit balances of all the active clients, it gives me a different balance per client if I do a query for each individual client. With a client base of a 100K and with a number of transactions over 2 million it isn't viable to do so. So here is what I do for an indiviudal client:

Code:

SELECT COUNT(Debit) as NumberOfDebits
       , COUNT(Credit) as NumberOfCredit
       , SUM(Debit) as DebitTotal
       , SUM(Credit) as CreditTotal 
FROM MemberTransactions 
WHERE MemberID = '####000094';

As I mentioned above, this gives the right balances for the member, but if I do the following:

SELECT MemberID
       , COUNT(Debit) as NumberOfDebits
       , COUNT(Credit) as NumberOfCredit
       , SUM(Debit) as DebitTotal
       , SUM(Credit) as CreditTotal 
FROM MemberTransactions 
GROUP BY MemberID;

It gives me both a different count and sum results for most the members in the table.

Here is the table structure so you can understand what I have to work with and what I want to accomplish. Every row is a single transaction with either a debit or a credit to the member's account. So what I want to do is sum up every debit and credit into a single cell for each respectively for every member. So that is why I was the group by, thinking that it would add up every credit and debit for every member, but it won't do that. So how would I go about that. I've tried to do an outer join on the membernr from the member details, but I still need to group by which gives me the same result in the end

Table Structure:

 PeriodNr                I
 EffectiveDate           D
 Entrynr                 +
 MemberNr                A
 Date                    D
 JournalNr               A
 ReferenceNr             A
 DtAmount                N
 CtAmount                N
 Narration               A
 ModifyUserId            A
 ModifStamp              @

One thing I did notice is that after I run the following query:

 SELECT COUNT(A.CtAmount) as CreditCount
       , Sum(A.CtAmount) as Credit
       , COUNT(A.DtAmount) as DebitCount 
       , SUM(DtAmount) as Debit
       , M.MemberNr
       , M.Premium  
 FROM MemAcc as A  
 LEFT OUTER JOIN Member as M on A.MemberNr = M.MemberNr  
 GROUP BY M.MemberNr, M.Premium;

There is a single row at top with no MemberNr and a significantly high number of counts, debit and credit. Much higher than any account should be, so I'm guessing for some reason that the missing transactions are going into this row for some reason.

For an example, if I uniquely query lets say member X, I get a debit and credit of 3094 and debit count of 55 and credit count of 18 which matches with the number of records that are in the table for that member, but when I run the above query I get a credit count of 2, debit count of 19, credit of 1590 and debit of 2090.

So I am stumped. I don't know if this is a Paradox problem, or rather my inept understanding of SQL.

Oh yeah the blank member has a credit count of 273, debit count of 341, credit of 19030 and debit of 17168.

Answer 1

I don't know if this is a Paradox problem, or rather my inept understanding of SQL.

I would expect that the resultset for the "single member" query and the equivalent line in the "all members" query would return the same counts and sums. If that was your expectation too then I wouldn't describe your understanding of SQL as "inept".

Diagnosing these sorts of problems is hard. The one clue you have is this:

It gives me both a different count and sum results for most the members in the table. (emphasis mine)

What you need to do is pick a couple of members where both queries return the same result and discover what distinguishes them from members which have different results.

Answer 2

The results you see from the third query suggest that you have a bunch of records in the MemAcc table where the MemberNr is null. Since there is no way to attach them to the proper member, they would all get grouped together, and the members would appear to have fewer MemAcc records.

Answer 3

The memberNr might not be NULL in the MemAcc table, just with the left outer join it is not on the Member table, but you are doing a group by the member table columns - therfore it shows as NULL because the MemAcc entry no longer exists in the Member table.

e.g. if you do this :

 SELECT COUNT(A.CtAmount) as CreditCount 
       , Sum(A.CtAmount) as Credit 
       , COUNT(A.DtAmount) as DebitCount  
       , SUM(DtAmount) as Debit 
       , A.MemberNr 
       , M.Premium   
 FROM MemAcc as A   
 LEFT OUTER JOIN Member as M on A.MemberNr = M.MemberNr   
 GROUP BY A.MemberNr, M.Premium; 

you will see different results - at least the value of MemberNr which no longer exists on Member.

As for your strange results. I seem to recall a limit on the number of rows in a Paradox table, and you could be approaching that limit. Of course might not be - and depends on which version of Paradox you are using, and how you are accessing the data.

In worse case scenarios, have seen the need to UNION a few of those queries together. E.g.

 SELECT COUNT(A.CtAmount) as CreditCount 
       , Sum(A.CtAmount) as Credit 
       , COUNT(A.DtAmount) as DebitCount  
       , SUM(DtAmount) as Debit 
       , A.MemberNr 
       , M.Premium   
 FROM MemAcc as A   
 LEFT OUTER JOIN Member as M on A.MemberNr = M.MemberNr   
 WHERE A.MemberNr <= 100000
 GROUP BY A.MemberNr, M.Premium; 

 UNION

 SELECT COUNT(A.CtAmount) as CreditCount 
       , Sum(A.CtAmount) as Credit 
       , COUNT(A.DtAmount) as DebitCount  
       , SUM(DtAmount) as Debit 
       , A.MemberNr 
       , M.Premium   
 FROM MemAcc as A   
 LEFT OUTER JOIN Member as M on A.MemberNr = M.MemberNr   
 WHERE A.MemberNr > 100000
 GROUP BY A.MemberNr, M.Premium;