in reply to How to combine the values from hash using 'cname', along with total and balance.
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
|
|---|
| Replies are listed 'Best First'. | |
|---|---|
|
Re^2: How to combine the values from hash using 'cname', along with total and balance.
by Sami_R (Sexton) on Jan 31, 2020 at 09:23 UTC |