RE: Elementary SQL question

How about this (run against order hdr/dtl files):

SELECT (COUNT(DISTINCT char(B.ODORD#)||char(B.ODOELN)) /
        COUNT(DISTINCT A.OHORD#))
FROM oepordh a left outer join oepordd b on A.OHORD#=B.ODORD#



The trick is to make sure the numerator (in my case, the order number||Order
line#) is a unique value. The distinct keyword removes duplicate entries, so
make sure you account for that.

Eric DeLong
Sally Beauty Company
MIS-Sr. Programmer/Analyst
940-898-7863 or ext. 1863



-----Original Message-----
From: Joe Pluta [mailto:joepluta@PlutaBrothers.com]
Sent: Friday, September 21, 2001 9:06 AM
To: midrange-l@midrange.com
Subject: RE: Elementary SQL question


You need count(*) of the transaction file divided by count(*) of the
customer master.  I can't see any way of doing that in one statement.

Joe

> From: Matthias Oertli
>
> Given a customer master file (one record per customer) and a transaction
> file (one record per transaction, one customer can have many
> transactions), how would it be possible to find the average number of
> transactions per customer with one single SQL statement?

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.