Perform addition of data from rows

htmanning has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I have a field in my database that contains a number. It represents the number of room keys someone has registered with us. When I query the database I can count the number of rows, so I know how many people have registered their key, but I need to also add the total number of keys. This must be simple, but I'm not sure how to do it. I count the rows like this:

$recordcount = $sth->rows;
[download]

But how do I count the data in the field labeled $keys?

Comment on Perform addition of data from rows Download Code

Replies are listed 'Best First'.
Re: Perform addition of data from rows by ww (Archbishop) on Jul 14, 2014 at 20:13 UTC
Let your database do it for you. check Ln42!	[reply]
Re^2: Perform addition of data from rows by RichardK (Parson) on Jul 14, 2014 at 22:17 UTC
You might find the sql functions sum() and count() rather useful.	[reply]
Re: Perform addition of data from rows by oakb (Scribe) on Jul 14, 2014 at 23:58 UTC
Doing everything in the database using SQL statements — as suggested by others here — is a very good option if you are just working within the database itself (i.e. inserting, updating). However, you posted your question to a Perl forum, not a SQL forum, so I will assume that you need to do something with the data in Perl. As such, you actually need to fetch table data (rather than table metadata such as that returned by commands such as `$sth->rows`). To do this, you need to make sure that your statement handle is correctly formulated to return the data you want, and then you will use some version of `fetchrow`: `my $key_count; while ( my ( $keys ) = $sth->fetchrow_array ) { $key_count += $keys; } # Do something with the total $key_count` [download] Hopefully, this is enough to point you in the right direction.	[reply] [d/l] [select]
Re^2: Perform addition of data from rows by htmanning (Friar) on Jul 15, 2014 at 02:56 UTC
Thank you. This suggestion pointed me in the right direction. I'm not sure I did it correctly, but it got me there. I used the while loop to add up the total amount of keys, but then had to query the database again to get the rest of the info. It's probably not efficient, but it works.	[reply]
Re: Perform addition of data from rows by PerlSufi (Friar) on Jul 14, 2014 at 20:38 UTC
I would look into using DBIx::Class to count a particular column. `use strict; use warnings; use DBIx::Class; my $rs = $schema->resultset('YOURTABLENAMEHERE')->search( {}, { columns => [ qw/name/ ], distinct => 1 } ); my $count = $rs->count;` [download] That was basically straight from here: https://metacpan.org/pod/distribution/DBIx-Class/lib/DBIx/Class/Manual/Cookbook.pod	[reply] [d/l]