count data and sum of data

Garima Bansal

2y892SQL

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?

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vishal Joshi2y ago
Accepted 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
+1

Jaimin Shethiya2y ago

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 Ansari2y ago

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;