Re^3: AVG returns COUNT

[DrAxeman]

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");

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
+.

Sorry to keep bothering with this.

[ChemBoy]

You are correct—you're missing the use strict that you commented out from the beginning of the file. :-)

Had you left it in, and left uncommented both print lines, you would have gotten (and probably did get) these errors:

Global symbol "$avg" requires explicit package name at runsql.pl line 
+20.
Global symbol "@r" requires explicit package name at runsql.pl line 21
+.

What those messages are telling you is that you have a variable you didn't declare using our, use vars or my. If you think they're wrong, you probably have done one of two things: tried to use an array or hash in place of a reference to one, or made a typo. In this case, conveniently enough, you have one of each. :-) If you comment out print ("@r \n") (by which you presumably meant print "@{$r} \n") and change my $agv to my $avg, you should see those errors go away (after you put strict back in so they show up, of course).

Then you just have the SQL error, which is annoying—I would class it as a bug in the DBD module, but in any case, it should go away if you change count(1) to count(*), or possibly count(ERWWCOMMUNITIES_Memory_Pages_sec). Or, since you don't really need it anyway, just take it out entirely.

And if you don't know what I mean by a reference to an array or hash, take a quick look at perlref and the always-popular References quick reference, and you shall be enlightened. :-)

Good luck!