It produces:use strict; use warnings; use Data::Dumper qw(Dumper); use DBI; my $aref = [ { 'cname' => 'Smart Parking', 'balance' => 10.12, }, { 'cname' => 'Smart Parking', 'balance' => 10.22, }, { 'cname' => 'Smart Parking', 'balance' => 10.32, }, { 'cname' => 'Highview Parking', 'balance' => 20.12, }, { 'cname' => 'Highview Parking', 'balance' => 20.22, }, { 'cname' => 'Highview Parking', 'balance' => 20.32, }, { 'cname' => 'Highview Parking', 'balance' => 20.42, }, { 'cname' => 'ParkingEye', 'balance' => 30.12, }, { 'cname' => 'ParkingEye', 'balance' => 30.22, }, ]; my $dbh = DBI->connect("dbi:SQLite:dbname=:memory:"); $dbh->do("CREATE TABLE parking ( cname TEXT, balance REAL )"); for my $row (@$aref){ $dbh->do("INSERT INTO parking (cname,balance) VALUES ('$row->{cname +}',$row->{balance})"); } my $sth = $dbh->prepare("SELECT cname, sum(balance) as balance, count( +*) as count FROM parking GROUP BY cname ORDER BY cname"); $sth->execute; my $result ; while (my $r = $sth->fetchrow_hashref()) { push @$result, { balance => $r->{balance}, cname => $r->{cname}, t +otal => $r->{count} } } print Dumper $result;
Plenty of room for making it more robust and improving performance .. left as an exercise.$VAR1 = [ { 'balance' => '81.08', 'total' => 4, 'cname' => 'Highview Parking' }, { 'balance' => '60.34', 'total' => 2, 'cname' => 'ParkingEye' }, { 'balance' => '30.66', 'total' => 3, 'cname' => 'Smart Parking' } ];
"From there to here, from here to there, funny things are everywhere." -- Dr. Seuss
In reply to Re: How to combine the values from hash using 'cname', along with total and balance.
by NetWallah
in thread How to combine the values from hash using 'cname', along with total and balance.
by Sami_R
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |