× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.