Maybe this SQL will help.

 SELECT AccNum, max(date), Type, sum(Amount) FROM transaction GROUP BY
+ AccNum, Type
union
SELECT AccNum, max(date),'Balance', sum(if (type='Debit',-1*Amount,Amo
+unt)) FROM transaction GROUP BY AccNum;

SELECT * FROM transaction t where chequeno is not null or ddno is not 
+null;
[download]

And this has little to do with perl this is an datbase question

Sir.For the question below, I want to solve it with a single SQL query. I tried all possibilities but nothing is working. Please help: Query data from transaction table and calculate total amount debited by cheque, dd and by cash for each account.

1) Query data from transaction table and calculate the total balance in each account

Select columns from your table, group by "accnum". When "type" = 'credit', then add "amount"; when "type" = 'debit', subtract "amount". And then display the result as a column in your resultset.

2) Query data from transaction table

SELECT columns FROM transaction table

and calculate total amount

sum(...)

debited by cheque,

chequeno IS NOT NULL

dd

ddno IS NOT NULL

and by cash

chequeno IS NULL AND ddno IS NULL

for each account.

GROUP BY accnum

For the rest, why not look here? And there.

#!/usr/bin/perl
use DBI; 
use strict;
use warnings;
print "content-type:text/html\n\n";
my $dbh = DBI->connect('dbi:mysql:database:3306','prithvi','prithvi') 
+or die("Couldn't connect"); 
my $tran_cur = $dbh->prepare("SELECT AcNo, Type, SUM(Amount) FROM bank
+tran GROUP BY AcNo, Type");
$tran_cur->execute;
map {print "<td>$_</td>"}qw(Account Number-Type-Total Amount);
print "<br/>";
while( my @data = $tran_cur->fetchrow_array)
{
 my $rec = join ('-',@data);
 print "$rec<br/>";
}
$tran_cur->finish;
$dbh->disconnect;
[download]