DBI SUM function

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

Greetings Monks,

I'm a high-beginner to intermediate at perl and DBI but this simple issue is eating my lunch.

I have a table of directory information that includes file sizes. I have been adding to the table, reading (select) from the table, etc. for months with no problems. Now I want to add up some selected file sizes. I keep hitting brick walls so I just ask about adding all the file sizes.

From the MySQL command prompt this works:
SELECT SUM(file_size) FROM dir_info;

But, using the same SQL statement inside perl returns a NULL.

With respect to my DB access I am patterning my code from the "MySQL and Perl for the Web" book by Paul DuBois (good book). At a high level I have:
* A DBI connect statement
* The SQL statement shown above stored in $SQLstr
* $sth = $dbh ->prepare($SQLstr);
* $sth -> execute();
* A while(@val = $sth->fetchrow_array()) loop

Many different SELECT statements work OK in my $SQLstr. Adding multiple "where this = that" statements connected by "AND" works OK. I can even use COUNT(*) with no problem. It seems odd to use fetchrow_array for a COUNT but I did not find another function and it worked so I was happy. But, no matter what I do I can't get SUM to work.

By bending the code around to get different error messages and Googling I have gotten hints of column vs. row operations and groupings. These ideas might be on the path to "truth" but it still seems that if the command line works then I should be able to do it in perl.

I have also gotten hints that I need to select in the SQL statement and then use SUM inside the while loop. This seems odd since a "SUM" inside the while loop would not be a SUM in a SQL statement.

Any suggestions or Wisdom?

Thanks,
Bruce

Comment on DBI SUM function

Replies are listed 'Best First'.
Re: DBI SUM function by Errto (Vicar) on Mar 25, 2006 at 17:18 UTC
How about a small sample Perl program that reproduces this behavior? For example, I just tried the following on my local setup: `use DBI; use strict; use warnings; my $dbh = DBI->connect('dbi:mysql:test','root','',{RaiseError => 1}) or die; my $sth = $dbh->prepare('select sum(x) from dummy'); $sth->execute(); while(my @val = $sth->fetchrow_array()) { print "$val[0]\n"; }` [download] and it worked like a charm. And yes, the while loop is redundant since this query can't return more than one row but I was trying to follow your model.	[reply] [d/l]
Re^2: DBI SUM function by Bruce32903 (Scribe) on Mar 25, 2006 at 18:08 UTC
Thanks for the suggestions. I have tried the suggestion from holli and the code provided above is so similar to my code that for a moment I thought it was mine. So far I still have my NULL problem. I was only able to sneak a quick look at my computer now so all I can say at the moment is an initial "Thank you". Later today I should be able to put together a small sample program that will either work (and shock me) or allow me to demonstrate exactly what is not working. So far the only differences I see between the above code and my code is my use of upper case for the SQL words (shouldn't matter) and my use of a $SQLstr variable inside the prepare statement (I don't think this matters). I'll post a test program later. Thank you, Bruce	[reply]
Re^3: DBI SUM function by Errto (Vicar) on Mar 25, 2006 at 22:32 UTC
One other thought. SUM will return NULL if the query without the SUM would return no rows or if all the rows returned contain NULL values for the column in question. Are you sure you're running the same query against the same database table in the command prompt vs Perl? Are you using the same username etc?	[reply]
Re^3: DBI SUM function by Bruce32903 (Scribe) on Mar 26, 2006 at 13:28 UTC
... Next morning ... A thousand pardons dear Monks. In my application I have batches of data that can be added from time to time. When I run my code I don't know for sure if a batch currently exists. Because of this my SQL statement (including a lot of WHERE stuff) will be expected to return a NULL from time to time. Thus I used a simple "foolproof" way of testing for a null return. ... Yes, you guessed it, my "foolproof" code was actually "foolish" code. Let's just label it an "Intro to Computing 101, week #1 error" and let it fade away. BUT, that leaves me with my current "foolproof code". my $found_something = 0; while (@val = $sth -> fetchrow_array()) { # useful database code goes here $found_something = 1; } if ($found_something == 0) { # "graceful" found nothing code goes here } This should work, but it has a bit of a "bailing wire and chewing gum" look to it. Is there a cleaner way to do it? Thanks for the help and sorry for error on my part. Bruce	[reply]
Re: DBI SUM function by holli (Abbot) on Mar 25, 2006 at 17:16 UTC
<wild_guess> Try `SELECT SUM(file_size) AS mysum FROM dir_info;` </wild_guess> holli, /regexed monk/	[reply] [d/l]
Re: DBI SUM function by eric256 (Parson) on Mar 27, 2006 at 15:37 UTC
You can simplify that using the `$dbh->selectrow_array($statement);` method. `my ($val) = $dbh->selectrow_array("SELECT sum(file_size) FROM dir_info +");` [download] ___________ Eric Hodges	[reply] [d/l] [select]