Counting Duplicate Record SQL on single Table

SELECT   INSTITUTE_ID, SL_NO, ROOM_NO, SIZE_NO, class_name, 
COUNT (*) dup_rec    FROM   class_rooms GROUP BY
INSTITUTE_ID, SL_NO, ROOM_NO, SIZE_NO, class_name  HAVING   COUNT (*) > 1
and INSTITUTE_ID is not null and SL_NO is not null and
ROOM_NO is not null and SIZE_NO is not null and   class_name is not null
order by INSTITUTE_ID desc, SL_NO, ROOM_NO, SIZE_NO, class_name;


























select INSTITUTE_ID, SL_NO, ROOM_NO, SIZE_NO, class_name from
class_rooms where institute_id=35065 and class_name='Six' order by institute_id;


SELECT INSTITUTE_ID, SL_NO, ROOM_NO, SIZE_NO, class_name,
COUNT(*) OVER(PARTITION BY INSTITUTE_ID, SL_NO, ROOM_NO, SIZE_NO, class_name) AS NumberOfDuplicates
FROM CLASS_ROOMS;

How to spool oracle sql output in xcel file

SQL> SET PAGESIZE 40000

SQL> SET FEEDBACK OFF

SQL> SET MARKUP HTML ON

SQL> SET NUM 24

SQL> SPOOL niger_duplicate.xls

SQL> SELECT INSTITUTE_ID,ROOM_NO, SIZE_NO,CLASS_NAME,
NO_BENCH,
NO_CHAIR,
NO_TABLE,
WRITNG_BOARD,
NO_FAN,
NO_LIGHT,
PARTITION_TYPE,
SL_NO,
MULTIMEDIA,
IS_ELECTRICITY
FROM class_rooms 
GROUP BY SIZE_NO,
CLASS_NAME,
NO_BENCH,
NO_CHAIR,
NO_TABLE,
WRITNG_BOARD,
NO_FAN,
NO_LIGHT,
PARTITION_TYPE,
INSTITUTE_ID,
SL_NO,
ROOM_NO,
MULTIMEDIA,
IS_ELECTRICITY 
HAVING count(*) > 1
order by SL_NO,institute_id;  

SQL>

SQL>SPOOL OFF

SQL>SET MARKUP HTML OFF

SQL>SPOOL OFF

 you can find the spool output in your current directory.

default directory