RE: STRQRY and counts.

["DeLong, Eric" <EDeLong@xxxxxxxxxxxxxxx>]

In SQL, you can use the count(distinct vvvvvvv) to distill the required 
counts....

SELECT count(distinct ID#) from .........

Often, when I'm prototyping a query and need to generate counts like this, I'll 
take my original query and wrap it in a "With" clause...

With t1 as
(
        place your complex query here....
)

Select count(distinct id#) as unique_customers
from t1

hth,
Eric

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of John Wallroff
Sent: Monday, March 05, 2007 10:44 AM
To: Midrange Systems Technical Discussion
Subject: STRQRY and counts.


I have a question on how to do what I think should be simple count in
the Query Utility.  I would like to be able to count how many times the
report breaks.

An example:  I create a query with 2 files.  The first file, the
Primary, contains a person's basic info such as gender, birthdate, etc.
The second file, joined by "Matched records with the primary file" by
ID#, contains all the addresses for a person.  Since a person can have
more then one address, perhaps a home address and a business address,
they can end up having multiple lines in the query output.  Let's say I
want a count of how many "Males" have an address in "Idaho".  I want the
output to contain every address in Idaho even if they have more then one
there.  I sort by ID# and have a break set up on that field as well.

Is there a way I can count how many people are included in the query?  I
want to count each person only once even though they may have multiple
lines because they have more then one address Idaho.  In other words I
want to count how many times there is a break.

The output should look something like this.

ID#     Name            Address
1       Bob             123 Main Street.  Boise, ID  12345
1       Bob             345 1st Street. Boise, ID 12345
2       Joe             678 2nd Street. Big Mountain, ID 23456
3       Sam             987 Madison Ave.        Little Steam, ID 34567
3       Sam             765 Adams Blvd. Deep Creek, ID 56789

Final Totals
Count 3

Right now the only "Count" I know how to do will give a total of 5.

I've never been able to figure out how to do this all in one single
query.  In the past I've had to run 3 passes.  The first one to print
out all the detail info I need.  I then run that one again and suppress
summaries to an output file, then I run another query over the output
file of the second to get a count of the number of lines.

Thanks.

John.