I have a table bills
which has several columns including bus_billed_amount
,uniform_billed_amount
, registration_number
and auto_incremented id
Now I grouped, using an id, depending on the required feature to give uniforms to the students. Let's say, a student has been billed for uniform this semester and next semester as well meaning they have to be treated separately in order to separate each billing despite the student has the same registration number
Now, my question is, how can I find total of the two billings yet bearing the fact that each billing of the student has to be shown???
$res = mysqli_query($con,"SELECT *, IF(status=1,'Active','Inactive') As status,
IF((SUM(r.uniform_billed) - (SELECT SUM(s.uniform_paid) FROM bill_payments AS s WHERE r.registration_number = s.registration_number AND r.status ='1'))> 0,'Payment Not Done','Paid') AS pay_status,
( SUM( r.uniform_billed ) - ( SELECT SUM(s.uniform_paid) FROM bill_payments AS s WHERE r.registration_number = s.registration_number AND r.status ='1' ) ) AS uniform_balance,
If(uniform_state='1','Uniform Given', 'Uniform Not Given') as given_state
FROM bills as r
WHERE r.uniform_state='0' AND r.status='1'
GROUP BY id
ORDER BY firstname ASC
LIMIT ".$per_page." OFFSET ".$offset);
mysqli_close($con);