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 -----
From: 'Martijn Tonies (Upscene Productions)' [email protected]
[firebird-support]
To: [email protected]
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 -----
From: mailto:[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.
---
This email is free from viruses and malware because avast! Antivirus protection
is active.
http://www.avast.com
