locked
sql sum function give x2 of correct sum value. RRS feed

  • Question

  • User774838899 posted

    The following query which give 400 value of totalDeduction
    while the tblDeduction table have only 1 deduction with value 200

    select e.EmpID, concat(e.FName, ' ', e.LName) as Name , o.HireDate, o.BasicPay, s.Scale, SUM(a.amount) as totalAllowance, SUM(d.Amount) as totalDeduction

    from tblEmployee e
    join tblEmpOfficialDetail o on e.EmpID=o.EmpID
    join tblScale s on o.ScaleID=s.ID
    join tblAllowance a on o.ScaleID=a.ScaleID
    join tblDeduction d on d.ScaleID=o.ScaleID group by e.EmpID, e.FName, e.LName, o.HireDate, o.BasicPay, s.Scale

    when i write a seperate query using same the logic gives the correct ans.

    select sum(d.amount), o.scale from tblDeduction d, tblScale o where d.ScaleID=o.ID group by o.Scale

    please Help
    Thanks

    Sunday, October 2, 2016 6:44 PM

All replies

  • User475983607 posted

    You have a 1-to-many relationship where one of the tables in the JOIN has a has two of the same foreign key values.  For example, an employee that has two hire dates or two official details records. 

    You'll need to figure this out on your own as we can't see your data.  An easy way to do this is looking at the JOIN ON clause then doing a SELECT on the IDs in each table to find the dup.  

    Sunday, October 2, 2016 7:00 PM
  • User-1404113929 posted

    hi,

    please try with bellow query

    select e.EmpID, concat(e.FName, ' ', e.LName) as Name , o.HireDate, o.BasicPay, s.Scale,
    allw.totalAllowance, ded.totalDeduction
    From tblEmployee e
    join tblEmpOfficialDetail o on e.EmpID=o.EmpID
    join tblScale s on o.ScaleID=s.ID
    Join 
    (
    select sum(amount)totalDeduction ,scaleId from tblDeduction  group by ScaleId
    )ded on ded.scaleId=s.Id
    Join 
    (
    select sum(amount) totalAllowance,scaleId from tblAllowance  group by ScaleId
    )allw on allw.scaleId=s.Id

    thanks,

    murali

    Friday, October 7, 2016 10:44 AM