Beefy Boxes and Bandwidth Generously Provided by pair Networks
laziness, impatience, and hubris
 
PerlMonks  

Comment on

( #3333=superdoc: print w/ replies, xml ) Need Help??
I have a 'transaction' table created in SQL like this:
TranID Date AccNum Type Amount ChequeNo DDNo 657520 02-07-1999 0181432 Debit 16000 465774 657524 02-07-1999 0181432 Debit 13000 569086 657538 09-07-1999 0181432 Credit 11000 657548 18-07-1999 0181432 Credit 15500 657519 02-07-1999 0181432 Debit 12000 657523 02-07-1999 0181432 Credit 11000 657529 03-07-1999 0181432 Debit 15000 466777 657539 10-07-1999 0181432 Credit 10000 657541 11-07-1999 0181432 Debit 12000 657525 03-07-1999 0181432 Debit 15000 569999 657533 05-07-1999 0181432 Credit 12500
The question is: Query data from transaction table and Calculate the total debit amount and total credit amount for each account. My script is like this:
#!/usr/bin/perl use DBI; use strict; use warnings; $dbh = DBI->connect('dbi:database','prithvi','prithvi') or die "Couldn +'t connect"; my $tran_cur = $dbh->prepare("SELECT AccountNumber, Type, SUM(Amount) +FROM transaction GROUP BY AccountNumber, Type;"); $tran_cur->execute; print "<table><tr>"; map {print "<td>$_</td>"}qw(Account Number-Total Debit Amount); print "<br/>"; print "</tr>"; print "</table>"; while( my @data = $tran_cur->fetchrow_array) { my $rec = join ('-',@data); print "$rec\n"; } $dbh->disconnect;
I am getting the exact output for this. But I have two more questions.
1) Query data from transaction table and calculate the total balance +in each account 2) Query data from transaction table and calculate total amount debite +d by cheque, dd and by cash for each account.
How can I do this? I just have to replace the SQL query in the above script for the 2 new questions. Please help. Thanks in advance

In reply to SQL Database Table to Perl Script by prithviraj

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • 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! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, 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, 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:
    & &amp;
    < &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.
  • Log In?
    Username:
    Password:

    What's my password?
    Create A New User
    Chatterbox?
    and the web crawler heard nothing...

    How do I use this? | Other CB clients
    Other Users?
    Others lurking in the Monastery: (8)
    As of 2015-07-31 02:21 GMT
    Sections?
    Information?
    Find Nodes?
    Leftovers?
      Voting Booth?

      The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









      Results (274 votes), past polls