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