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-13T19:20:06Z
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.