Counting Unique Records

Posted on 2011-05-10
Last Modified: 2012-05-11
I'm using Crystal XI

I have a report that lists jobs per cost center.  Some jobs can show up on different cost centers.  If I count records (how I'm currently doing it), I get more jobs than are actually on the report.

My current query is something like:

Open in new window

which returns 150 records.  This is not the number of jobs, since a job might appear in two or more rows with differing cost centers.
What I need is something like

Open in new window

which will return 85, the actual number of jobs.

These, of course, would be filtered through Crystal's Record Selection Formulae.

Is there a way to count just the unique records based on a single field?
Question by:Clif
    LVL 77

    Accepted Solution

    If you use Insert Summary on the Job field then you can choose Distinct Count.
    LVL 100

    Expert Comment

    If you are trying to do it in the SQL try

    SELECT DISTINCT Job_No, Count(Job_No) as JobCount FROM MyView

    LVL 11

    Author Closing Comment

    DistinctCount was exactly what I was looking for.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Internet Business Fax to Email Made Easy - With eFax Corporate (, you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 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

    10 Experts available now in Live!

    Get 1:1 Help Now