Hello Mike, select count(CASE_NUMBER), CASE_NUMBER from ACCT_CASE_COURT group by CASE_NUMBER having count(CASE_NUMBER) > 1
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: Tuesday, October 28, 2014 1:27 PM To: [email protected] Subject: Re: [firebird-support] How do I count the number of duplicate rows in a table? Hi Martijn, I did finally come up with this SELECT DISTINCT(ACC.CASE_NUMBER), (SELECT COUNT(ACC2.ACCT_CASE_COURT_ID) FROM ACCT_CASE_COURT ACC2 WHERE ACC2.CASE_NUMBER = ACC.CASE_NUMBER) AS CNT FROM ACCT_CASE_COURT ACC GROUP BY 1 Nut, I like yours better. Now I need to exclude any that have a count less than 2 and I know I have to use HAVING but have not got it figured out yet. Mike ----- Original Message ----- From: mailto:[email protected] [firebird-support] To: [email protected] Sent: Tuesday, October 28, 2014 7:18 AM Subject: Re: [firebird-support] How do I count the number of duplicate rows in a table? select count(case_number) as counted, case_number from ACCT_CASE_COURT group by case_number ? 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: Tuesday, October 28, 2014 1:13 PM To: [email protected] Subject: [firebird-support] How do I count the number of duplicate rows in a table? Greetings All, Firebird 1.5.3 Should be elementary but, I'm drawing a blank on how to accomplish this. I have a table (ACCT_CASE_COURT) that contains these fields (among others): ACCT_CASE_COURT_ID INTEGER NOT NULL PK ACCT_ID INTEGER NOT NULL CASE_ID SMALLINT NOT NULL CASE_NUMBER VARCHAR(20) NOT NULL ... How do I form a SQL Select statement that would contain CASE_NUMBER in the first column (sorted) and number of times that the case number is found in the table in the second column (CNT)? Any help truely appreciated. Thanks, Mike ------------------------------------------------------------------------------ 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.
