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: 'Martijn Tonies (Upscene Productions)' m.ton...@upscene.com
[firebird-support]
To: firebird-support@yahoogroups.com
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:firebird-support@yahoogroups.com
Sent: Tuesday, October 28, 2014 1:13 PM
To: firebird-support@yahoogroups.com
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.
http://www.avast.com
