Solved

SQL query to find duplicate values using the HAVING clause

Posted on 2014-09-25
3
272 Views
Last Modified: 2014-09-25
I'm trying to find duplicate values using the HAVING clause, but the SQL query below is returning the following error: "SQL command not properly ended". What am I doing wrong?

SELECT enrol.rep_start_year, 
         uni.university_code,
         enrol.student_oen,
         COUNT(*) AS duplicate_oen_count
         
    FROM (SELECT rep_start_year, period,
                     student_oen,
                 fee_cat_type_id,
                 uni_institution_type_id,
                 reg_status_type_id
            FROM edcs_unistat.ustat_enrolment_data) enrol
         INNER JOIN edcs_unistat.uni_institution_type uniinst
             ON enrol.uni_institution_type_id = uniinst.uni_institution_type_id
         INNER JOIN
         (SELECT u.university_id,
                 (CASE
                      WHEN u.university_code = 'DOMC' THEN 'CARL'
                      WHEN u.university_code = 'NOSM-LAKE' THEN 'NOSM'
                      WHEN u.university_code = 'NOSM-LAUR' THEN 'NOSM'
                      WHEN u.university_code = 'LAUR-ALGM' THEN 'ALGM'
                      WHEN u.university_code = 'LAUR-HRST' THEN 'HRST'
                      WHEN u.university_code = 'DOMC' THEN 'CARL'
                      ELSE u.university_code
                  END)
                     AS university_code
            FROM edcs_core.university u) uni
             ON uniinst.university_id = uni.university_id
         INNER JOIN edcs_unistat.reg_status_type regstatus
             ON enrol.reg_status_type_id = regstatus.reg_status_type_id
         INNER JOIN edcs_unistat.fee_cat_type feetyp ON enrol.fee_cat_type_id = feetyp.fee_cat_type_id

where       enrol.rep_start_year in ('2012', '2013')
AND regstatus.reg_status_type_code IN ('12','13','14','15','16','18')
AND feetyp.fee_cat_type_code IN ('3', '4')
and         enrol.period in ('FAL')

GROUP BY enrol.rep_start_year, uni.university_code, enrol.student_oen
ORDER BY enrol.rep_start_year, uni.university_code, enrol.student_oen      

HAVING COUNT(*) > 1

Open in new window

0
Comment
Question by:mariita
3 Comments
 
LVL 34

Expert Comment

by:johnsone
ID: 40344557
First thing I would do is get rid of the blank lines.  Some tools don't like them.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 40344564
Move the order by.  HAVING is part of the GROUP BY:
GROUP BY enroll.rep_start_year, uni.university_code, enroll.student_oen
HAVING COUNT(*) > 1
ORDER BY enroll.rep_start_year, uni.university_code, enroll.student_oen
0
 

Author Closing Comment

by:mariita
ID: 40344622
Thanks!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now