locked
aggregate function in sql shows duplicate rows while joining RRS feed

  • Question

  • User1052662409 posted

    Hi All,

    I am trying to fetch the records by joining two tables for all the employees.

    Below is my query

    SELECT  (select name from tbl_Employee where sno=TEI.imp_id) AS Name, (SELECT Location FROM tbl_Projects where Sno=TEI.imp_emp_location) AS Location, SUM(TEI.imp_amount_approved_by_finance) AS Imprest, SUM(TE.amount) AS Amount, SUM(TE.reco_amount) AS Reco, SUM (TE.approved_amount) AS Approved,SUM(TE.audit_amount) AS Audit
      FROM tbl_emp_imprest TEI 
       LEFT JOIN tblExpenditure TE ON
      TE.emp_id =TEI.imp_id
      WHERE TEI.imp_amount_approved_by_finance>0 AND
      DATEPART(year,TEI.imp_fin_appr_date) =DATEPART(YEAR,GETDATE()) AND  DATEPART(month,TEI.imp_fin_appr_date) =DATEPART(Month,GETDATE()) AND DATEPART(year,TE.date) =DATEPART(YEAR,GETDATE()) AND  DATEPART(month,TE.date) =DATEPART(Month,GETDATE())
      GROUP BY TEI.imp_id,TEI.imp_emp_location,TEI.imp_amount_approved_by_finance,TE.amount,TE.reco_amount,TE.approved_amount

    The result of the above query is as below with duplicate rows.

    Capture

    Whereas I was willing to get the result like below.

    Name         Location                    Imprest     Amount    Reco     Approved     Audit

    R Gupta     Dumpsite Auditor     35000         5500          0             0                 0

    Why it is making the duplicate records and not summing the amount where I have used the SUM()?

    Wednesday, September 30, 2020 7:02 PM

Answers

  • User452040443 posted

    Hi,

    Try removing columns TEI.imp_amount_approved_by_finance, TE.amount, TE.reco_amount and TE.approved_amount from Group By:

    GROUP BY TEI.imp_id, TEI.imp_emp_location
    

    Hope this help

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, September 30, 2020 7:23 PM