Using SQL to count non existant records!

[camidon]

As strange as this sounds, I need to count some records that I know don't exist and return 0

I have a list of people who are doing projects, some of them have done several and some have done none. I want to create a query to list those users but I need to return a row for all of the guys who have done no projects and count the number of projects they have done as 0.

Any ideas?

 

[IdoMillet]

Outer Join, to keep these records in.

Create a formula:
IF IsNull({Proj_ID}) Then 1 ELSE {Proj_ID}

Count the formula, rather than the {Proj_ID}

Cheers,
- Ido

Visual CUT & DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[lbass]

What version of CR are you using? Do you have the option of using SQL expressions?

-LB

 

[synapsevampire]

Join the people table to the projects table using a Left Outer Join.

Group by the people id.

In the Report->Edit Selection Formula->Group place something like:

count({projects.id},{people.id}) = 0

Of course I've made a lot of assumptions because you've supplied nothing technical about your environment.

For best results, post:

Crystal version
Database/connectivity used
Example data
Expected output

-k

 

[camidon]

Thanks for the replies guys. I'm using a CR 8.5 Environment so I believe I can use SQL expressions. Not sure about that though.

I'm just pulling this info out of one table, however because I'm actually querying a view which already has the names that I need included in it.

So is there a way to do this when just working with one table??

 

[lbass]

If the only time that ProjID is null is when there are NO project IDs for a person, then you could use a formula like:

if isnull({table.projID}) then 999 else {table.projID}

Then you could group on this and insert a count of {table.projID} at the group level.

Otherwise, you could create a SQL expression like {%count}:

(select count(AKA.`projID`) from Table AKA where
AKA.`personID` = Table.`personID`)

Make sure you substitute the exact field names for "projID" and "personID" and the correct table name for "Table". Leave "AKA" as is, as it is an alias table name.

Then create a formula {@grp}:

if {%count} = 0 then 999 else {tableprojID}

Then insert a group on {@grp} and insert a sum on the following formula which is placed in the detail secton:

if isnull({table.projID}) then 0 else 1

-LB

 

[synapsevampire]

You state that you only have one table, and then say however I'm using a View... This means that you have more than one datasource which means that Crystal expects the data to be joined (although there are tricks).

Try posting technical information:

Database/connectivity used
Example data (show tables/views/joins/example data)
Expected output

-k

 

[davidwendelken]

I'm new to CR, so I'll give my example in SQL. Others know better than I do how to translate this SQL into the necessary steps in the CR report wizards.

This is in Oracle SQL syntax. (Just don't use the ansi syntax much.) The (+) is an outer join instruction. Where it's located, it means to include all employees in the employee table even if there are no matching records in the work_assignment table.

This is pretty basic SQL, so most SQL databases can handle its equivalent.

select 
 e.empno
,count(wa.projectid) as number_of_projects
from 
 employees       e
,work_assgnments wa
where e.empno = wa.empno (+)
group by e.empno

David Wendelken

http://www.casetech.net

 

[Turkbear]

Hi,
Try this:
Add the 2 tables to your Crystal report and link them
on the empno field ( Left Outer Join from Employee to Work_assignments, please).

In your report Group on {employees.empno}..
Create a formula:
If (IsNull({work_assignments.project_id}) or
Trim({work_assignments.project_id}) = "")
then
0
else 1

Place this in the details section..( supress its output and/or the whole details section)..
Insert a summary ( sum)of this formula.Place it in the GF for the Employee GH.

Hope it gives a idea..







To Paraphrase:"The Help you get is proportional to the Help you give.."