RE: Elementary SQL question

Let's define what "average transaction per customer" is.  In most cases, it
would be the total number of transactions divided by the total number of
customers.  If you had 30 transactions spread over 3 customers, the average
transactions per customer is 10.

Examine the following situation:

Customer A, 12
Customer B, 11
Customer C, 7

My calculation and yours, Buck, would yield the same result: 10.  However,
take the following:

Customer A, 25
Customer B, 5
Customer C, 0

My calculation would still yield 10, but since you would only be dealing
with TWO groups (there would be no selection for Customer C), you would get
a value of 15.  This would be valid if the question were: calculate the
average transactions per customer for customer with one or more
transactions.

That's the basic limitation of using set-based processing; it's not very
good about dealing with null sets.  You could still do this with a single
SQL statement, but you'd have to use COUNT(*) and a WHERE clause in your
subselect.  Whereas with an RPG program and a couple of API calls (to get
the record counts), you'd be done in a jiffy.

Joe


> -----Original Message-----
> From: midrange-l-admin@midrange.com
> [mailto:midrange-l-admin@midrange.com]On Behalf Of Buck Calabro
> Sent: Friday, September 21, 2001 10:28 AM
> To: midrange-l@midrange.com
> Subject: RE: Elementary SQL question
>
>
> >> 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?
>
> >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.
>
> I guess I don't understand what is being asked.  If I do a count of
> transactions divided by a count of masters, doesn't that do funny
> things if
> say, all the transactions belong to one master?
>
> Here's my submission:
>
> select avg(count) from
>   (select count(*) as count from transactionfile
>      group by customernumber) as average
>
>   --buck
> _______________________________________________
> 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.
>
>