CODEWITHSUNDEEP
mysqlsql
Ahsan Malik
Ahsan Malik

Reputation: 111

sum of two columns in two tables mysql

i am developing a LMS system for an institute and i am trying to develop a recovery report on the end of month the report contains student name total fee package, total received, total receiveable, current month pending installment

here is the installment data of a student with his admission id enter image description here

and this is the ledger data from where ican pick the fee package and total receiveable fees

enter image description here

and i am using this query for recovery report

SELECT 
SUM(l.dr)-SUM(l.cr) as sum_remaining, 
f.dr as fee_package, 
SUM(i.payment) as this_month_install,
a.reg_id, s.fname
FROM
ledger l, ledger f, student_data s, 
admissions a LEFT OUTER JOIN installments i ON a.admissionid = i.admissionid
WHERE 
a.admissionid = '58ac4b5421488' AND
a.reg_id = s.reg_id AND
l.reference = '58ac4b5421488' AND
l.details <> 'registration fee' AND
f.reference = '58ac4b5421488' AND
f.details = 'Fee Package' AND  
i.install_no <> '1' AND 
MONTH(i.pay_date) = '04' AND 
YEAR(i.pay_date) = '2017'
GROUP BY a.admissionid

and its giving the result like this

enter image description here

but the result should be like

sum_remaining = 10000 and this_month_install = 10000 please help me to sort out this problem Thanks in advance

Upvotes: 0

Views: 148

Answers (2)

Ahsan Malik
Ahsan Malik

Reputation: 111

i have done this with a sub query thanks all

select  a.admissionid, s.fname, 
sum(l.dr)-SUM(l.cr) as sum_remaining, i.*, 
f.dr as fee_package from student_data s,  
ledger l, ledger f, admissions a 
RIGHT outer join (select admissionid, 
 sum(payment) as this_month_install 
 from 
 installments g where g.install_no <> '1' and MONTH(g.pay_date) = '04' and YEAR(g.pay_date) = '2017' group by g.admissionid) i

ON 
i.admissionid = a.admissionid where a.reg_id = s.reg_id and 
a.status = 'studying' and a.course = 'PH' and 
a.campus = 'CIFSD01' and l.reference = a.admissionid and 
l.details <> 'registration fee' and f.reference = a.admissionid 
and f.details = 'Fee Package' GROUP BY a.std_id

Upvotes: 0

ScaisEdge
ScaisEdge

Reputation: 133370

you should start from admission and use inner join for the others table (left join for installments)

  SELECT 
    SUM(l.dr)-SUM(l.cr) as sum_remaining, 
    f.dr as fee_package, 
    SUM(i.payment) as this_month_install,
    a.reg_id, 
    s.fname
  FROM admissions a
  Inner JOIN ledger f ON f.reference = a.admissionid AND f.details = 'Fee Package' 
  INNER JOIN ledger l  ON l.reference = a.admissionid AND l.details <> 'registration fee'  
  INNER JOIN student_data s ON a.reg_id = s.reg_id
  LEFT  JOIN installments i ON a.admissionid = i.admissionid   AND i.install_no <> '1' 
  WHERE a.admissionid = '58ac4b5421488' 
  AND  MONTH(i.pay_date) = '04' 
  AND  YEAR(i.pay_date) = '2017'
  GROUP BY a.admissionid

you have two row in installments table that match .. try filter just one 

SELECT 
SUM(l.dr)-SUM(l.cr) as sum_remaining, 
f.dr as fee_package, 
SUM(i.payment) as this_month_install,
a.reg_id, 
s.fname
FROM admissions a
Inner JOIN ledger f ON f.reference = a.admissionid AND f.details = 'Fee Package' 
INNER JOIN ledger l  ON l.reference = a.admissionid AND l.details <> 'registration fee'  
INNER JOIN student_data s ON a.reg_id = s.reg_id
LEFT  JOIN installments i ON a.admissionid = i.admissionid 
                        AND i.install_no not in ( '1', '2') 
WHERE a.admissionid = '58ac4b5421488' 
 AND  MONTH(i.pay_date) = '04' 
AND  YEAR(i.pay_date) = '2017'
GROUP BY a.admissionid

Upvotes: 1

Related Questions