Jsp report with mysql 2005

[mrabrar09]

Hello Everyone...

Am little bit confuse whether this post is come under Jsp or MySQl... but am posting in JSP because am thinking that it jsp..not Mysql query

I created one report but the report is not coming perfectly so i need your help

My actual report format is like this Example.

SnoCustomerID----CName--Trn_Date------Place---Amount-------SubTotal----ProductName--Prodtno
1   -     A12    -     Xyz -  03-08-2011  -   Hyd  -   1000   -         -    ****   -    12345
2   -     A12    -     Xyz -  03-08-2011  -   Hyd  -    200   -  1700   -     **    -    14465
3   -     A12    -     Xyz -  03-08-2011  -   Hyd  -    500   -         -     ***   -    12552
4   -     A12    -     Xyz -  04-08-2011  -   Hyd  -   2000   -         -    ***    -    41555
5   -     A12    -     Xyz -  04-08-2011  -   Hyd  -   1500   -  3500   -     **    -    44455
6   -     A13    -     ABC -  08-08-2011  -   J&K  -   2500   -  2500   -    ***    -    25445
7   -     A13    -     ABC -  09-08-2011  -   J&K  -   1000   -         -    &&&4   -    54545
8   -     A13    -     ABC -  09-08-2011  -   J&K  -    200   -  1200   -    ASDA   -    54555

To get the above report I used Group by clause on four column customerID,CName,Trn_Date and Place

Am using below query to get above report but am getting repeating values in SubTotal

select ProductName,Prodtno,CustomerID,CName,Trn_Date,Place,Amount,tot.SubTotal from cust_table a inner join(select CustomerID,CName,Trn_Date,Place,sum(debit) as SubTotal from cust_table where financial_year='"+fyear+"' and tr_dt BETWEEN convert(datetime,'"+trfromdt+"',103) and convert(datetime,'"+trtodt+"',103) group by CustomerID,CName,Trn_Date,Place) tot  on a.tr_Date=tot.tr_Date and a.cname=tot.cname and a.customerID=tot.customerID 

Am using Jsp to dispaly the data.....if am using above query the o/p is coming like this.

Sno    CustomerID   CName  Trn_Date      Place   Amount  SubTotal  ProductName  Prodtno
1        A12         Xyz   03-08-2011     Hyd     1000     2000       ****       12345
2        A12         Xyz   03-08-2011     Hyd      500     2000        **        14465
3        A12         Xyz   03-08-2011     Hyd      500     2000       ***       12552
4        A12         Xyz   04-08-2011     Hyd     2000     1700       ***        41555
5        A12         Xyz   04-08-2011     Hyd     1500     1700        **        44455
6        A13         ABC   08-08-2011     J&K     2500     2500       ***        25445
7        A13         ABC   09-08-2011     J&K     1000     1200       &&&4       54545
8        A13         ABC   09-08-2011     J&K      200     1200       ASDA       54555

So, Try to use distinct keyword on one column but we cannot use the distinct keyword on one column...

Please let me know how to get above result.

Thanks
Abrar

[mrabrar09]

No reply.....