total number of duplicates in oracle table

Question

I have a table like below

SUBJECT Years MARKS
AB      1       20
AB      1       25
AC      1       20
AC      1       30
AC      1       40
AD      1       20

I only need count of duplicates (subject||Year), expected answer is 2 and not

AB1 -- 2
AC1 -- 3

what means "expected answer is 2"? what is the result expected? — Florin Ghita, Aug 27 '12 at 13:46
Possible duplicate of [Count duplicates records in Mysql table?](https://stackoverflow.com/questions/12528644/count-duplicates-records-in-mysql-table) — tkruse, Jan 15 '18 at 05:22

score 8 · Accepted Answer · answered Aug 27 '12 at 14:00

That would be one more query on top of the duplicates query...

select subject, year, count(*)
  from table1
  group by subject, year
  having count(*) > 1

will give you all the results with counts. Another count over this..

select count(*)
  from (
select subject, year, count(*)
  from table1
  group by subject, year
  having count(*) > 1
)

should give you the number of records which have one or more duplicates.

vikiiii · Answer 2 · 2012-08-27T14:53:21.117

1

select subject,years, count(*)
from table
group by subject,years
having count (*) > 1;

edited Aug 27 '12 at 14:53

answered Aug 27 '12 at 13:47

vikiiii

score 0 · Answer 3 · answered Aug 27 '12 at 14:03

0

Just use an inline view over the query you used to get your example (AB1, AC1) like this:

select count (*) from (select subject, years from t group by subject, years having count (*) > 1)

answered Aug 27 '12 at 14:03

Lord Peter

3 Answers3