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:

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.

Would anyone have an idea why the sum() and count() would be different with the second query?

Thanks a lot in advance

In the first query you extract a specific userid in the second it performs an aggregate of all the users.
So your SUM is the total for ALL users (unless you only have the one user)

http://www.w3schools.com/sql/sql_groupby.asp

But shouldn't the GROUP BY seperate them by memberID, as the final number in the group by query is actually lower than the single member query.

and does the COUNT(column) in the GROUPBY query match the number of records returned?

Without seeing the relationships between the values returned and the table structure and data it is impossible to say definitively why.

But a COUNT(column) returns the number of rows in the dataset returned by the query.

So if memberid nnn had 50 rows in the database the count(debits) would return 50 whereas a GROUPBY would aggregate the rows into groups so there may NOT be 50 rows in the dataset.

Assuming the Paradox SQL engine is standardised.

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 as you pointed out 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 my 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.


Thanks again for the help that you have already given me.