On Fri, May 18, 2012 at 5:07 AM, Rob Dixon <rob.di...@gmx.com> wrote:
> 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
Thanks for the help Rob and Jose! I kept digging are and searching
Google for more knowledge and found the solution. It appears that you
cannot use a WHERE clause when using Aggregate functions. The SELECT
statement works with this now:
my $all = $dbh->selectall_arrayref("SELECT employee_id, date,
SUM(hours_wrkd) FROM data GROUP BY employee_id, date HAVING MIN(date)
>= $date_then AND MAX(date) <= $date_now ORDER BY hours_wrkd ASC" );
Again thanks guys!!!
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
