HERE I have query where i need to get all rows based on EMPID and BranchID in one row but here it is giving result like this one : SELECT DISTINCT (ed.EmpID), (ab.Branch_ID), (SELECT SUM(CASE WHEN cs.IsAccepted IS NULL AND cs.IsRejected IS NULL THEN 1 ELSE 0 END) FROM Cust_ExpressIntrestLintStatus cs WHERE cs.ToCustId = emu.CreatedByCustID GROUP BY ExpressinterstID ) AS Pending, SUM(CASE WHEN ces.IsAccepted = 1 THEN 1 ELSE 0 END) AS Accepted, SUM(CASE WHEN ces.IsRejected = 1 THEN 1 ELSE 0 END) AS Rejected, COUNT(Distinct emu.Emp_FollowupTicket_ID ) AS Assigned FROM Cust_BasicInfo cb LEFT JOIN Cust_Details cd ON cd.Cust_ID = cb.Cust_ID LEFT JOIN Cust_Login cl ON cl.Cust_ID = cd.Cust_ID LEFT JOIN Emp_Details ed ON ed.EmpID = cb.ProfileOwnerEmpID LEFT JOIN Adm_Branch ab ON ab.Branch_ID = Ed.BranchID LEFT JOIN Emp_MatchFollow_UpTicket emu ON emu.CreatedByCustID = cb.Cust_ID OR emu.CreatedByCustID = cb.Cust_ID LEFT JOIN Cust_ExpressIntrestLintStatus ces ON EMU.ExpressintrestID = CES.ExpressIntrestLintStatus_ID LEFT JOIN Cust_ProfileExpressInterest cpl ON cpl.Cust_ProfileInterestsLog_ID = ces.ExpressinterstID WHERE ab.Branch_ID IS NOT NULL AND ed.EmpID IS NOT NULL GROUP BY ed.EmpId,Ab.Branch_ID,emu.CreatedByCustID
I am getting output like this :EmpID Branch_ID Pending Accepted Rejected Assigned2 9 NULL 0 0 02 9 0 0 1 12 9 0 1 0 13 8 NULL 0 0 03 8 0 1 0 14 8 NULL 0 0 015 8 NULL 0 0 0
i need to get output like this :EmpID Branch_ID Pending Accepted Rejected Assigned2 9 NULL 1 1 13 8 NULL 1 0 14 8 NULL 0 0 015 8 NULL 0 0 0
here observe empid and branchid are unique and rest of all case condition. i have used group by and having and max condition but result is not getting in correct wayP.V.P.MOhan