Subject Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?
Author Softtech Support
Post date 2014-10-13T19:16:32Z

Greetings Martijn,
 
Just happening to be testing this in Database Workbench.  Have used this product for years and just love it.
 
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 ,"
 
      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?    
 
Mike

 
----- 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.
 
Thanks,
Mike
 



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