Conditional Counts 1

[Yazster]

Hi,

I've been trying to write a formula, and can't seem to figure it out, hoping for some help...

I have two databases, both linked by a matching field containing a six digit number.

Database A:
Contains a list of distinct 6 digit numbers.

Database b:
This is a detailed database, containing many records where the six digit number can repete. It also includes a YES/NO field.

What I need:
I need a report, where for every 6 digit number in Database A, I get a count of the amount of records where YES appears in the YES/NO field in database B.

In SQL this is simple, but can't seem to find the correct syntax for the formula.

Any help would be greatly appreciated.

 

[kenhamady]

First group on the 6 digit number.

Then create a formula:

If {field} = 'yes' then 1 else 0

Then insert a summary of this field using the operation SUM, at that group level.



You could also create a cross-tab that sums this formula. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[MalcolmW]

Yazter,
The way SCR works in the example above, this is what is happening:

select a.idfield,
sum(case when b.ynfield = true then 1
else 0 end)
from a inner join b on a.idfield = b.idfield
group by a.idfield

This is obviously not the most efficient SQL to do this, and if you could add a where clause you might get better performance:

select a.idfield,
count(a.idfield)
from a inner join b on a.idfield = b.idfield
where b.ynfield = true
group by a.idfield

The way to do that in Crystal would be to add to the record selection formula something like:
{ynfield} = "YES"
and then just do a simple count of {idfield} for each {idfield} group.

Unless you have a big table here, or speed is important, then this change won't make any significant difference. But I thought it would help illustrate the different ways of using formulas in SCR translates into different SQL created by SCR. Malcolm
wynden@telus.net
November is "be kind to dogs and programmers" month. Or is that "dogs or programmers"?