Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved?

['Softtech Support' stwiz...@att.net [firebird-support]]

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 ----- 
  From: 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com 
[firebird-support] 
  To: firebird-support@yahoogroups.com 
  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!


  From: mailto:firebird-support@yahoogroups.com 
  Sent: Monday, October 13, 2014 8:50 PM
  To: firebird-support@yahoogroups.com 
  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.
http://www.avast.com