[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Counting Unique Records

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:
SELECT * 
FROM MyView

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
SELECT COUNT(DISTINCT Job_No)
FROM MyView

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?
0
Clif
Asked:
Clif
1 Solution
 
peter57rCommented:
If you use Insert Summary on the Job field then you can choose Distinct Count.
0
 
mlmccCommented:
If you are trying to do it in the SQL try

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

mlmcc
0
 
ClifAuthor Commented:
DistinctCount was exactly what I was looking for.

Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now