charles07 Posted March 8, 2012 Share Posted March 8, 2012 guys, please help i need balance amount of fees all students has to pay. Following is the DB structure. `feepayid` is primary key for paymentstable and paymentregister & `studentid` is primary in studentstable. I have written a query for this, please evaluate the query. paymentstable feepayidclassidpaydatepaymodefeeamountremarksfeediscount 3282012-03-06 19:32:351500hgfhf0 3182012-03-04 19:32:351800hgfhf0 3082012-02-06 19:32:3511200hgfhf0 2982012-02-06 19:32:3511100hgfhf0 paymentregister feepayidpayidtotalfeestudentid 32315002 31235002 30145004 29355004 studentstable studentidstudentname 2john 4mathew 5peter 6mary SELECT d.`studentid`, SUM(a.`totalfee` - (SELECT SUM(`feeamount`) FROM `paymentstable` WHERE a.`feepayid` = `feepayid`)) AS balancefee FROM `studentstable` d LEFT JOIN `paymentregister` a ON a.`studentid` = d.`studentid` GROUP BY d.`studentid`; Link to comment https://forums.phpfreaks.com/topic/258505-mysql-for-select-using-sum-in-another-table/ Share on other sites More sharing options...
blacknight Posted March 8, 2012 Share Posted March 8, 2012 did it work? i would have joined the paymentstable insted of having a imbeded select query tho.... Link to comment https://forums.phpfreaks.com/topic/258505-mysql-for-select-using-sum-in-another-table/#findComment-1325124 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.