count data and sum of data

Question

in code, i am using .net repeater to get the data and I want the sum of the total application by SQL query,

SELECT sec.Id, Sec.Name as SName
   ,CASE WHEN SUM(DAS.Approved) IS NULL THEN 0 ELSE SUM(DAS.Approved) END as Approved
   ,CASE WHEN Sum(DAS.Pending) IS NULL THEN 0 ELSE SUM(DAS.Pending) END as Received
   ,CASE WHEN SUM(UnderProcess) IS NULL THEN 0 ELSE SUM(Process) END as UnderProcess
   FROM [dbo].[Sectors] Sec
   LEFT JOIN [dbo].[PMasters] AS A
       ON Sec.Id = A.AltSectorId
   LEFT JOIN D_AStatistics AS DAS
       ON A.Id = DAS.ActivityID
       GROUP BY Sec.Name,Sec.Id
       order by 1

How I can I do?

Vishal Joshi · Answer

Hello, The query given looks good. Please check the data and mapping . i.e. primary key and foreign key data before executing the query. Thanks Vishal Joshi

Jaimin Shethiya · Answer

Hello Garima, Try this one SELECT S.Id, S.Name AS SName ,CASE WHEN SUM(D.Approved) IS NULL THEN 0 ELSE SUM(D.Approved) END AS Approved ,CASE WHEN Sum(D.Pending) IS NULL THEN 0 ELSE SUM(D.Pending) END AS Received ,CASE WHEN SUM(UnderProcess) IS NULL THEN 0 ELSE SUM(Process) END AS UnderProcess ,(CASE WHEN SUM(D.Approved) IS NULL THEN 0 ELSE SUM(D.Approved) END + CASE WHEN SUM(D.Pending) IS NULL THEN 0 ELSE SUM(D.Pending) END + CASE WHEN SUM(UnderProcess) IS NULL THEN 0 ELSE SUM(Process) END) AS TotalApplication FROM [dbo].[Sectors] AS S LEFT JOIN [dbo].[PMasters] AS A ON S.Id = A.AltSectorId LEFT JOIN D_AStatistics AS D ON A.Id = D.ActivityID GROUP BY S.Id, S.Name ORDER BY 1

Tahir Ansari · Answer

try this code SELECT sec.Id, Sec.Name as SName, CASE WHEN SUM(DAS.Approved) IS NULL THEN 0 ELSE SUM(DAS.Approved) END as Approved, CASE WHEN SUM(DAS.Pending) IS NULL THEN 0 ELSE SUM(DAS.Pending) END as Received, CASE WHEN SUM(DAS.UnderProcess) IS NULL THEN 0 ELSE SUM(DAS.UnderProcess) END as UnderProcess, (CASE WHEN SUM(DAS.Approved) IS NULL THEN 0 ELSE SUM(DAS.Approved) END + CASE WHEN SUM(DAS.Pending) IS NULL THEN 0 ELSE SUM(DAS.Pending) END + CASE WHEN SUM(DAS.UnderProcess) IS NULL THEN 0 ELSE SUM(DAS.UnderProcess) END) as TotalApplication FROM [dbo].[Sectors] Sec LEFT JOIN [dbo].[PMasters] AS A ON Sec.Id = A.AltSectorId LEFT JOIN D_AStatistics AS DAS ON A.Id = DAS.ActivityID GROUP BY Sec.Name, Sec.Id ORDER BY 1;