On 18/05/2012 02:09, Omega -1911 wrote:
On Thu, May 17, 2012 at 7:58 PM, jbiskofski<jbiskof...@gmail.com> wrote:
On Thu, May 17, 2012 at 6:46 PM, Omega -1911<1911...@gmail.com> wrote:
While trying to create a script that generates a graph, I am at a loss
for a workable solution. What I need to be able to do in the code
below is add the results from the select statement before pushing the
data into an array. For example, the database contains the following
data:
EMPLOYEE_ID - HOURS_WRKD - DATE
--------------------------------------------------------
David - 8.5 - 1336432429
Marty - 7.5 - 1336432429
David - 5.0 - 1336432429
Steve - 8.0 - 1336432429
Elana - 6.5 - 1336432429
Marty - 8.0 - 1336432429
What I need to be able to do is add the employee's total hours before
pushing it into the array. As such, the following is what would be
pushed into the arrays below:
David - 13.5 - 1336432429
Marty - 15.5 - 1336432429
Steve - 8.0 - 1336432429
Elana - 6.5 - 1336432429
--------%<------ACTUAL CODE ------------%<-----------------
my $dbh = DBI->connect( "dbi:SQLite:dbname=$database_name", "", "" );
my $all = $dbh->selectall_arrayref("SELECT employee_id, hours_wrkd,
date FROM data WHERE date>= $date_then AND date<= $date_now ORDER BY
hours_wrkd DESC" );
foreach my $row (@$all)
{
($employee_id, $hours_wrkd, $date) = @$row;
print "\n\n$employee_id, $hours_wrkd, $date \n";
push(@DATABASE_NAMES, $employee_id);
push(@DATABASE_HOURS, $hours_wrkd);
}
-------------------------------------------------------------------------
The easiest thing would be to rewrite your sql with an aggregate statement :
SELECT employee_id,date,SUM(hours_wrkd)
FROM data WHERE date>= $date_then AND date<= $date_now
GROUP BY empoyee_id,date
ORDER BY 3
Thanks for the help Jose, but it is still not adding the data
correctly. As you will see, the total hours for David should be 84,
not the 42 as shown in the graph. (I have two entries in the table
with 42 hours each.)
The SQL that Jose wrote looks fine. Perhaps the WHERE clause is
excluding some data that it shouldn't?
Here is a program using SQLite that creates a table with your sample
data and generates the aggregate output that you ask for. It may be
useful to experiment with it to see where your program fails.
HTH,
Rob
use strict;
use warnings;
use feature 'say';
use DBI;
my $dbh = DBI->connect("dbi:SQLite:dbname=test","","", { RaiseError =>
1}) or die $!;
$dbh->do('DROP TABLE IF EXISTS employees') or die $!;
$dbh->do('CREATE TABLE employees (employee_id text, hours_wrkd num, date
int)');
while (<DATA>) {
tr/-//d;
my @data = split;
my $sth = $dbh->prepare('INSERT INTO employees (employee_id,
hours_wrkd, date) VALUES (?, ?, ?)');
$sth->execute(@data) or die $!;
}
say "Table data";
my $dump = $dbh->selectall_arrayref('SELECT * FROM employees');
say join ' - ', @$_ for @$dump;
say '';
my $group = $dbh->selectall_arrayref(<<'SQL');
SELECT employee_id, sum(hours_wrkd) as total_hours, date
FROM employees
GROUP BY employee_id
ORDER BY total_hours DESC
SQL
say "Aggregate data";
say join ' - ', @$_ for @$group;
say '';
__DATA__
David - 8.5 - 1336432429
Marty - 7.5 - 1336432429
David - 5.0 - 1336432429
Steve - 8.0 - 1336432429
Elana - 6.5 - 1336432429
Marty - 8.0 - 1336432429
**OUTPUT**
Table data
David - 8.5 - 1336432429
Marty - 7.5 - 1336432429
David - 5 - 1336432429
Steve - 8 - 1336432429
Elana - 6.5 - 1336432429
Marty - 8 - 1336432429
Aggregate data
Marty - 15.5 - 1336432429
David - 13.5 - 1336432429
Steve - 8 - 1336432429
Elana - 6.5 - 1336432429
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
