Subject Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Author Martijn Tonies (Upscene Productions)
Post date 2014-10-13T20:06:53Z
Hello Mike,
 
So you want to count records in DEBT for a specific CLT_ID.
 
Now, in the DEBT table, there are records for multiple PERSONs, but what about ACCT_ID,
CASE_ID and DEBT_NO, which of these or what combination are unique with regard to each
ACCT_CASE?
 
If none, you would need a derived table, but these are available in Firebird 2 onward.
 
The alternative is to create a VIEW for the DISTINCT query and COUNT on that.
 
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
 
 
Sent: Monday, October 13, 2014 9:57 PM
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
 

Hi Martijn,
 
I knew I was going to get in trouble by not providing enought information as I thought by proving less it would be just a little bit clearer to understand, my bad...
 
Let's start over with an simplified explanation of the tables:
 
ACCT_CASE: Case Management table
ACCT_ID    INTEGER    NOT NULL    PK
CASE_ID    SMALLINT    NOT NULL    PK
CLT_ID    INTEGER    NOT NULL    FK to CLIENT table  << Need this for the JOIN
...
 
DEBTOR_CASE_DEBT:  Allows for multiple PERSON's to be associated with a DEBT
ACCT_ID    INTEGER    NOT NULL    PK
CASE_ID    SMALLINT    NOT NULL    PK
DEBT_NO    SMALLINT    NOT NULL    PK
PERSON_ID    INTEGER    NOT NULL    PK
STATUS_DATE    TIMESTAMP    NOT NULL
STATUS_CODE    CHAR(1)    NOT NULL
 
What am I attempting to do?  I need to know how many records are in the DEBTOR_CASE_DEBT table that have a STATUS_DATE between '09/01/14' and '09/30/14' and the STATUS_CODE = 'B" (Bankruptcy Filed) and is for a specific CLT_ID (thus the join to ACCT_CASE to use CLT_ID).  I do not want to include the PERSON_ID when fetching a COUNT() of the record, I only need to know how many debts are in this status for the client.  So only concerned with ACCT_ID, CASE_ID and DEBT_NO.
 
So this SQL will return the correct number of records, now I just have to figure out how to return a count in one record.
 
      SELECT DISTINCT DCD.ACCT_ID, DCD.CASE_ID, DCD.DEBT_NO
                 FROM DEBTOR_CASE_DEBT DCD
                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
                  AND AC.CASE_ID = DCD.CASE_ID
                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
                  AND DCD.STATUS_CODE = 'B'
                  AND AC.CLT_ID = :V_CLT_ID
Did I provide enough information this time?  If not feel free to ask...
 
Thanks so much,
Mike
 
 
----- Original Message -----
Sent: Monday, October 13, 2014 2:20 PM
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
 
 

Hello Mike,
 
>Just happening to be testing this in Database Workbench.  Have used this product for years and just love it.
 
Thank you, that’s good to hear.
 
>Thanks for you reply.
> 
>So my second SQL should have been as follows?  It results in an error "Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char 34 ,"
 
COUNT only works on single column or *, so using COUNT on two columns won’t work.
 
I’m not sure what you’re trying to DISTINCT here, as the previous query counted PERSON_ID values in the result set.
 
>      SELECT COUNT(DISTINCT DCD.ACCT_ID, AC.CLT_ID)
>                 FROM DEBTOR_CASE_DEBT DCD
>                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
>                  AND AC.CASE_ID = DCD.CASE_ID
>                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
>                  AND DCD.STATUS_CODE = 'B'
>
>So not sure if this is how I should have done it, but it appears to work as it returns 20
> 
>      SELECT COUNT(DISTINCT DCD.ACCT_ID || AC.CLT_ID)
>                 FROM DEBTOR_CASE_DEBT DCD
>                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
>                  AND AC.CASE_ID = DCD.CASE_ID
>                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
>                  AND DCD.STATUS_CODE = 'B'
> 
>Is that correct?    
 
Don’t think this will work for all combinations of ACCT_ID and CLT_ID, imagine:
 
101 || 1
 
is the same as
 
10 || 11
 
Question is: what exactly are you trying to get from your query?
 
 
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
 
 
 
----- Original Message -----
Sent: Monday, October 13, 2014 1:56 PM
Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
 
 

Hello Mike,
 
In the second query, you’re counting records and then do the DISTINCT, so the
result is 32, and if you “distinct” that result, there’s only 1 record, with a value
of 32.
 
What you seem to want, is to COUNT(DISTINCT(...))
 
With regards,

Martijn Tonies
Upscene Productions
http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!
 
 
Sent: Monday, October 13, 2014 8:50 PM
Subject: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
 

Greetings All,
 
Firebird 1.5.3 (Yes I know it is old)
 
Using the following syntax with 09/01/14 and 09/04/14 for the parameters fetches 20 distinct records
 
      SELECT DISTINCT DCD.ACCT_ID, AC.CLT_ID
                 FROM DEBTOR_CASE_DEBT DCD
                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
                  AND AC.CASE_ID = DCD.CASE_ID
                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
                  AND DCD.STATUS_CODE = 'B'
Using this syntax with 09/01/14 and 09/04/14 for the parameters fetches a count of 32
 
      SELECT DISTINCT COUNT(DCD.PERSON_ID)
                 FROM DEBTOR_CASE_DEBT DCD
                 JOIN ACCT_CASE AC ON AC.ACCT_ID = DCD.ACCT_ID
                  AND AC.CASE_ID = DCD.CASE_ID
                WHERE DCD.STATUS_DATE BETWEEN :V_BEGIN_DATE AND :V_END_DATE
                  AND DCD.STATUS_CODE = 'B'
 
How do I accurately return the correct count using the COUNT() function?  In this case it should beturn 20 not 32
 
Any ideas appreciated.
 
 


This email is free from viruses and malware because avast! Antivirus protection is active.