http://www.perlmonks.org?node_id=997398


in reply to Re: SQL Database Table to Perl Script
in thread SQL Database Table to Perl Script

What are the additional fields Sir? Please help with the full SQL query.

Replies are listed 'Best First'.
Re^3: SQL Database Table to Perl Script
by james2vegas (Chaplain) on Oct 05, 2012 at 07:17 UTC
    I do not know the additional fields (how would I). They would be in your schema.
Re^3: SQL Database Table to Perl Script
by roboticus (Chancellor) on Oct 05, 2012 at 15:11 UTC

    prithviraj:

    You'll want something like:

    -- compute the totals for each account: select AccNum, sum(DB_amt) ttl_DB_amt, sum(CR_amt) ttl_CR_amt from ( -- transform the table from (accnum, type, amount) into -- (accnum, cr_amt, db_amt) select accnum, case when type='Credit' then Amount else 0 end CR_amt, case when type='Debit' then Amount else 0 end DB_amt from YOUR_TABLE_NAME where CHOOSE_WHICH_ACCOUNT(S)_YOU_WANT ) group by AccNum

    The inner select converts your table into a simpler view of account number, credit amount and debit amount. The outer select generates the totals for the simple view.

    Update: For your other questions, simply make the appropriate view transformations and accumulate the totals as above.

    ...roboticus

    When your only tool is a hammer, all problems look like your thumb.