SQL to obtain total SUM of a column and all entries between 2 dates

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

Dear All,

I have a table ( called Files_U ) like so, and I have 2 questions


File_name, Size_Byte, Date
Mydoc.doc, 1234, 11/12/2005
My.xls,2753,11/05/2004
fgf.pdf,90765,01/07/2001
the sheep and cucumber.mdb,4567356,01/01/2003
jojo-elliot.ppt,45675465,08/12/1995
hello_doly,8672341,12/9/1966
[download]

I need total sum of the sizes of all entries (i.e the total sum of the “Size_Byte” Column). So I wrote this bit of Code;

#! c:/perl/bin/perl.exe -slw
use strict;

use Win32::ODBC;

my $DSN = 'DD1';
my $DB = new Win32::ODBC($DSN) || die "Error => $DSN $!\n";

my $total = $DB->sql("SELECT SUM(Size_Byte) FROM Files_U WHERE Type ='
+doc'");
#print "$total"; this is not correct
$DB->Close;
[download]

my sql statement seems to be ok (no errors), but what I don’t know how to do is to print out the returned value! i.e the total sum of the values in the Size column.

My other question is; how can I create an sql statement that returns all the values in between two different dates?

Can I rely on your help and guidance in this please and maybe a quick example?

Thanks

Blackadder

Comment on SQL to obtain total SUM of a column and all entries between 2 dates Select or Download Code

Replies are listed 'Best First'.
Re: SQL to obtain total SUM of a column and all entries between 2 dates by davidrw (Prior) on Jul 20, 2005 at 17:52 UTC
for the second question, use the SQL 'BETWEEN' operator: `SELECT * FROM yourtable WHERE datcol BETWEEN '2005-01-25' AND '2005- +07-31'` [download] As for the primary question, look at the docs for Win32::ODBC. The `->Sql( SQL_STRING )` method Executes the SQL command SQL_STRING on the current connection. Returns ? on success, or an error number on failure. To get the data, you'll need to use the `FetchRow` method. This should help to get started and to see the structure returned: `my $rc = $DB->sql("SELECT SUM(Size_Byte) FROM Files_U WHERE Type ='doc +'"); die "ERROR: $rc" if $rc; use Data::Dumper; while( my $row = $DB->FetchRow ){ warn Dumper $row; }` [download]	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: SQL to obtain total SUM of a column and all entries between 2 dates
by davidrw (Prior) on Jul 20, 2005 at 17:52 UTC

  SELECT * FROM yourtable WHERE datcol BETWEEN '2005-01-25' AND '2005-
+07-31'
[download]

Win32::ODBC

->Sql( SQL_STRING )

Executes the SQL command SQL_STRING on the current connection. Returns ? on success, or an error number on failure.

FetchRow

my $rc = $DB->sql("SELECT SUM(Size_Byte) FROM Files_U WHERE Type ='doc
+'");
die "ERROR: $rc" if $rc;
use Data::Dumper;
while( my $row = $DB->FetchRow ){
  warn Dumper $row;
}
[download]

[reply]
[d/l]
[select]