Hi, I have queried results from a database as hash reference. Now, I wish to put these results in a data structure. The results are in order based on the first three columns. I am trying to get the following structure:

Joe=>{
      A=>{  1=> { [85,80],[90,99] }
            2=> { [50,60,87],[70,65,89] }
         }
      B=>{  1=> { [82],[92] }
            3=> { [30],[51] }
         }
     }
Rob => continue as above
[download]

Basically, I need to do calculations on the last two columns: Joe,A,1's would get calculated, Joe,A,2's would get calculated,Joe,B,1's would get calculated etc. Please let me know if there is a better approach. I was having trouble separating the rows Joe,A,1's from Joe,A,2's and therefore i was not able to do any calculations. If there is a way just to separate the rows based on the first three columns, then I know how to do the rest. Thanks in advance! Here is the code with the subset of data I queried:

#!/usr/bin/perl

use DBI;
use DBD::mysql;


print "Connecting...\n";

my $platform = "mysql";
my $database = "db_name";
my $host = "host_name";
my $port = "port_num";
my $tablename = "hist";
my $user = "user_name";
my $pwd = "pass";

open(STDOUT, ">C:\\perlscripts\\new.txt") || die "Can't open the file"
+;

my $dsn = "dbi:mysql:$database:$host:$port";
my $dbh = DBI->connect($dsn,$user,$pwd) || die "Could not connect: $DB
+I::errstr\n";

my $query = $dbh->selectall_arrayref("select e_id,cus,ta,gd1,gd2 from 
+hist
                                      order by e_id,cus,ta", 
                                      {Slice => {} });

foreach my $ref (@$query) {
   print "$ref->{e_id},$ref->{cus},$ref->{ta},$ref->{gd1},$ref->{gd2}\
+n";
}

<p>
Here is the subset of data I get from this code.
</p>

Joe,A,1,85,90
Joe,A,1,80,99
Joe,A,2,50,70
Joe,A,2,60,65
Joe,A,2,87,89
Joe,B,1,82,92
Joe,B,3,30,51
Rob,A,1,64,77
Rob,B,2,20,32
[download]

At the end, I want to be able to calculate the 4th and 5th column like this:

Joe,A,1,85,90
Joe,A,1,80,99
  Total 165,189
Joe,A,2,50,70
Joe,A,2,60,65
Joe,A,2,87,89
  Total 147,154
Joe,B,1,82,92
  Total 82,92
Joe,B,3,30,51
  Total 30,51
Rob,A,1,64,77
  Total 64,77
Rob,B,2,20,32
  Total 20,32
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.