Need help reporting distinct records with count

Posted on 2007-10-08
Last Modified: 2013-12-07
I would like to run a quick report that goes through a given table and reports all the duplicate records with a count of each one using sqlplus. Here's some sample data:

This should return a report something like:

 ID       COUNT
-----  ------------
   8           4
   9           1
 10           1
 11           1
 12           1

Can someone show me the query that can accomplish this?
Question by:mikestan
    1 Comment
    LVL 34

    Accepted Solution

    select id, count(1)
    from <tab>
    group by id;

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
    This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

    728 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

    22 Experts available now in Live!

    Get 1:1 Help Now