Re: DBI and fetchall_arrayref (gosh!)

I'm not sure if they are standard, but MySQL has 'grouping' operators that work on selects, including average, min, max, and count:

SELECT AVERAGE(cat1), AVERAGE(cat2), ... FROM table
[download]

You'd run this as a separate query from the one that gets the row averages.

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

Comment on Re: DBI and fetchall_arrayref (gosh!) Download Code

Replies are listed 'Best First'.
Re: Re: DBI and fetchall_arrayref (gosh!) by maderman (Beadle) on Nov 16, 2001 at 17:31 UTC
Hi Michael. Yes there is an average function in Oracle, but the form of my SQL statement would take the form: `$sth = $dbh->prepare(qq{ SELECT avg(DECODE(cat1, -1,null,cat1)), . . . avg(DECODE(cat15, -1,null,cat15)) FROM TABLE });` [download] The reason for this monster: well entries in columns can be '-1' (indicating the category was not applicable at entry time). So in order to get statistics, I have to change the -1's to nulls - that way Oracle doesn't include the nulls in the AVG calculations.... Regards, Stacy.	[reply] [d/l]

Replies are listed 'Best First'.

Re: Re: DBI and fetchall_arrayref (gosh!)
by maderman (Beadle) on Nov 16, 2001 at 17:31 UTC


$sth = $dbh->prepare(qq{
  
  SELECT 
   avg(DECODE(cat1, -1,null,cat1)),
   .
   .
   .
   avg(DECODE(cat15, -1,null,cat15))
      FROM TABLE
      });
[download]

[reply]
[d/l]