Hi, I do not follow whole discussion but what problem you see in Select count(field1 ¦¦ '_' ¦¦ field2) from ...
You can include more fields Regards, Karol Bieniaszewski Wysłane z mojego HTC ----- Reply message ----- Od: "'Softtech Support' [email protected] [firebird-support]" <[email protected]> Do: <[email protected]> Temat: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? Data: pon., paź 13, 2014 22:19 Hi Martijn, I want to count the number of records in the DEBTOR_CASE_DEBT table (not DEBT) where the ACCT_ID, CASE_ID and DEBT_NO are distinct and specific to CLT_ID. Is this possible in v1.5.3? If not and I need to create a view and COUNT() that, can you provide an example or tell me where to find info on doing that? Mike ----- Original Message ----- From: 'Martijn Tonies (Upscene Productions)' [email protected] [firebird-support] To: [email protected] Sent: Monday, October 13, 2014 3:06 PM Subject: Re: [firebird-support] How do I return an accurate COUNT(*) when a JOIN is involved? 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 ToniesUpscene Productionshttp://www.upscene.comDownload Database Workbench for Oracle, MS SQL Server, Sybase SQLAnywhere, 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 ToniesUpscene Productionshttp://www.upscene.comDownload Database Workbench for Oracle, MS SQL Server, Sybase SQLAnywhere, 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 ToniesUpscene Productionshttp://www.upscene.comDownload Database Workbench for Oracle, MS SQL Server, Sybase SQLAnywhere, 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. This email is free from viruses and malware because avast! Antivirus protection is active.
