Reputation: 805
Here am having two tables namely rent and student_hostel.the rent table looks like this
id date stud_id paid balance
18 10-2016 94 15000 15000
19 10-2016 94 10000 5000
20 10-2016 96 25000 5000
21 10-2016 96 5000 0
my student_hostel table looks like this..
id first_name last_name stud_id admit_date hostel class room bed status
94 ss ff PHBH00094 01-10-2016 12 16 115 501A P
96 maltu uv PHBH00096 01-10-2016 12 16 115 501C p
In order to get the last inserted stud_id's balance i used my code like this,
public function rent_outstanding($hos,$dt)
{
$sql = "select s.stud_id ,s.admit_date ,s.class,first_name,sum(paid) as rt_paid,balance,rt.stud_id
from student_hostel s, rent rt where s.id=rt.stud_id and hostel=? and rt.date=?
and rt.id = (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt')
and status!= 'G' and status!= 'R' GROUP BY rt.stud_id";
$query=$this->db->query($sql, array($hos, $dt));
return $query;
}
the problem am facing here is i was not able to sum the values comes under the paid columns of same stud_id. the output am getting is like this
SI.No STUDENT ID NAME RENT PAID BALANCE
1 PHBH00094 Ss 30000 10000 5000
2 PHBH00096 Maltu 30000 5000 0
the desired output i need to get is like this
SI.No STUDENT ID NAME RENT PAID BALANCE
1 PHBH00094 Ss 30000 25000 5000
2 PHBH00096 Maltu 30000 30000 0
Upvotes: 1
Views: 62
Reputation: 2711
Your final solved query is :
select s.stud_id ,s.admit_date ,s.class,first_name,sum(paid) as rt_paid,
(select r.balance from rent r where r.stud_id = rt.stud_id and r.date='$dt' order by r.id desc limit 1) as balance ,rt.stud_id
from student_hostel s join rent rt on s.id=rt.stud_id where hostel=? and rt.date=?
and status!= 'G' and status!= 'R' GROUP BY s.stud_id
I have removed this line in your existing query rt.id = (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt')
your (select max(id) from rent r where r.stud_id = rt.stud_id and r.date='$dt')
means all time getting only one last row rt.id
like 19 (for PHBH00094)
or 21 (for PHBH00096)
.
that's why you getting all time one last calculation.
Upvotes: 1