Hello Mike, So you want to count records in DEBT for a specific CLT_ID.
Now, in the DEBT table, there are records for multiple PERSONs, but what about ACCT_ID, CASE_ID and DEBT_NO, which of these or what combination are unique with regard to each ACCT_CASE? If none, you would need a derived table, but these are available in Firebird 2 onward. The alternative is to create a VIEW for the DISTINCT query and COUNT on that. 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 9:57 PM To: [email protected] Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? 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: mailto:[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.
