Subject Re: [firebird-support] How do I count the number of duplicate rows in a table?
Author Martijn Tonies (Upscene Productions)
Post date 2014-10-28T12:30:31Z
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!
 
 
 
 
 
Sent: Tuesday, October 28, 2014 1:27 PM
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 -----
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!
 
 
Sent: Tuesday, October 28, 2014 1:13 PM
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.