in reply to Combine data in table

It's just a single SQL statement, which should be easy since you have it in DBD::CSV already.. 
  SELECT
    colIP,
    SUM(colPartitionSize) as size,
    SUM(colPartitionFree) as free
  FROM table_name
  GROUP BY colIP

[download]
Otherwise, to do it in perl, you would loop over the rows, hashing on the IP. Something like: 
my $rows = ...; # ref to AoH
my %ips;
foreach my $row (@$rows){
  $ips{ $row->{colIP} }->{ colPartitionSize } += $row->{colPartitionSi
+ze};
  $ips{ $row->{colIP} }->{ colPartitionFree } += $row->{colPartitionFr
+ee};
}
print Dumper \%ips;

[download]

Replies are listed 'Best First'.
Re^2: Combine data in table
by DrAxeman (Scribe) on Aug 07, 2005 at 18:02 UTC
    This is what I did:

    # Connect to the database
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' => "file.csv"};

my $row = "IP,Partition,PartitionSize,PartitionFree";
my %ips;
foreach my $row (@$row){
  $ips{ $row->{colIP} }->{ colPartitionSize } += $row->{colPartitionSi
+ze};
  $ips{ $row->{colIP} }->{ colPartitionFree } += $row->{colPartitionFr
+ee};
}
print Dumper \%ips;

    [download]

    But I am getting:
    Can't use string ("IP,Partition,PartitionSize,Parti") as an ARRAY ref 
+while "strict refs" in use at ./psfile.pl line 39.

    [download]
[reply]
[d/l]
[select]
      I would recommend using the "SELECT ... GROUP BY" SQL statement -- it's very conscise and exactly what you want, and leverages the fact you're using DBD/CSV ..


      As for your code in this post, though, you have my $row = "IP,Partition,PartitionSize,PartitionFree"; which just sets some string as the value for $row .. you need it to be all the data as an array of hashrefs .. 
      my $rows = $dbh->selectall_arrayref("SELECT * FROM your_table", {Slice
+=>{}}, );
my %ips;
foreach my $row (@$rows){
  $ips{ $row->{colIP} }->{ colPartitionSize } += $row->{colPartitionSi
+ze};
  $ips{ $row->{colIP} }->{ colPartitionFree } += $row->{colPartitionFr
+ee};
}
print Dumper \%ips;

      [download]
      (Note that this is inefficient, as well -- would be better to use a $sth and fetch one row at a time instead of sucking in all the rows at once)
[reply]
[d/l]
[select]
        I got the Perl loop method to work (mostly for the experience). But now I'm working on the SQL method. My code looks like:

        # 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' => "psinfooutputdiskfile.
+csv"};

my @rows = $dbh->selectrow_arrayref("SELECT IP, SUM(PartitionSize), SU
+M(PartitionFree) FROM results GROUP BY IP");

print ("@rows\n"); # Just here to view output

        [download]

        I'm getting 

        SQL ERROR: Can't find table names in FROM clause!


Execution ERROR: No command found!.

        [download]

        Not sure what is wrong here.

        it I modify the SELECT statement to:
        my @rows = $dbh->selectrow_arrayref("SELECT IP FROM results");

        [download]

        It works. But if I try to include a GROUP BY or SUM I get the error.
[reply]
[d/l]
[select]
        Using this method, how would I print out %ips? I'd like data comming out to like the original data:

        IP,PartitionFree,PartitionSize
1.2.3.4,23452,234322
2.3.4.5,99348,8666623

        [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom