AVG returns COUNT

DrAxeman has asked for the wisdom of the Perl Monks concerning the following question:

Hello,
I am new to perl, so forgive me.

I need to calculate the average for a bunch of columns in a csv file. I googled and found some code and after playing I am getting results. The problem is, I'm getting the return of the COUNT function, not AVG. Any ideas why?

Thanks

#!/usr/bin/perl
    use strict;
    use warnings;
    use DBI;

    # Connect to the database, (the directory containing our csv file(
+s))

    my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;");

    # Associate our csv file with the table name 'prospects'
    $dbh->{'csv_tables'}->{'prospects'} = { 'file' => 'test.csv'};

    my $sth = $dbh->do("SELECT avg(ERWWCOMMUNITIES_Memory_Pages_sec) F
+ROM prospects");

    print ("$sth \n");
[download]

Comment on AVG returns COUNT Download Code

Replies are listed 'Best First'.
Re: AVG returns COUNT by japhy (Canon) on Aug 05, 2005 at 20:37 UTC
do() returns the number of rows of data returned. If you want to get the actual value, you'll need to do: `my $sth = $dbh->prepare("SELECT avg(...) FROM prospects"); $sth->execute; my ($avg) = $sth->fetchrow_array;` [download] There's probably a shorter way to do that though... Update: shorter way: `my ($avg) = $dbh->selectrow_array("SELECT avg(...) FROM prospects");` [download] Jeff `japhy` Pinyan, P.L., P.M., P.O.D, X.S.: Perl, regex, and `perl` hacker How can we ever be the sold short or the cheated, we who for every service have long ago been overpaid? ~~ Meister Eckhart	[reply] [d/l] [select]
Re^2: AVG returns COUNT by ChemBoy (Priest) on Aug 05, 2005 at 22:14 UTC
Why not just use SQL? `my $r = $dbh->selectall_arrayref("SELECT avg(...), count(1) FROM prosp +ects"); my ($avg,$count) = @{$r->[0]};` [download] update: Just as you were saying. Not sure how I managed that bad a mis-read while retaining enough brain cells to type straight... <blush> If God had meant us to fly, he would never have given us the railroads. --Michael Flanders	[reply] [d/l]
Re^3: AVG returns COUNT by DrAxeman (Scribe) on Aug 06, 2005 at 01:17 UTC
I tried this, and I think I am missing something. Here is what my script looks like: #!/usr/bin/perl #use strict; use warnings; use DBI; use Text::CSV; # Connect to the database, (the directory containing our csv file( +s)) my $dbh = DBI->connect("DBI:CSV:f_dir=.;csv_eol=\n;"); # Associate our csv file with the table name 'results' $dbh->{'csv_tables'}->{'results'} = { 'file' => 'test.csv'}; # my ($avg) = $dbh->selectrow_array("SELECT avg(ERWWCOMMUNITIES_Mem +ory_Pages_sec) FROM results"); my ($r) = $dbh->selectall_arrayref("SELECT avg(ERWWCOMMUNITIES_Mem +ory_Pages_sec), count(1) FROM results"); my ($agv,$count) = @{$r->[0]}; # print ("$avg,\n"); print ("@r \n"); [download] My output is `Possible unintended interpolation of @r in string at ./runsql.pl line +41. Name "main::r" used only once: possible typo at ./runsql.pl line 41. SQL ERROR: Bad table or column name '1' starts with non-alphabetic cha +racter! Execution ERROR: No command found!. Use of uninitialized value in array dereference at ./runsql.pl line 18 +.` [download] Sorry to keep bothering with this.	[reply] [d/l] [select]
Re^4: AVG returns COUNT by ChemBoy (Priest) on Aug 06, 2005 at 01:38 UTC
Re: AVG returns COUNT by borisz (Canon) on Aug 05, 2005 at 20:37 UTC
try it this way: `my ( $avg ) = $dbh->selectrow_array(q{ SELECT avg(ERWWCOMMUNITIES_Memory_Pages_sec) FROM prospects});` [download] Read DBI. Boris	[reply] [d/l]
Re: AVG returns COUNT by DrAxeman (Scribe) on Aug 05, 2005 at 22:49 UTC
Thanks!	[reply]
Re: AVG returns COUNT by themage (Friar) on Aug 07, 2005 at 15:18 UTC
From the DBI perldoc: Returns the number of rows affected or undef on error. A return value of -1 means the number of rows is not known, not applicable, or not available. So, you have to use some other method as specified above, to get the select result, the do only returns the number of afected rows. TheMage http://themage.bliker.com	[reply]