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)' [email protected]
[firebird-support]
To: [email protected]
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:[email protected]
Sent: Monday, October 13, 2014 8:50 PM
To: [email protected]
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
