Apply multiple count conditionally in SQL Server query

I have a task table which contains some DateTime type columns like completed, pending, exception. It joins user table and I want to bring
userName, AllTaskCount, AllPendingTaskCount, AllCompletedTaskCount, AllExceptionTaskCount.

If Completed columns has value than it will be considered as completed task, while if completed column is null with value in exception column then it is ExceptionTask and if neither Completed column has value nor exception one than it is pending task.

So I want to apply multiple count conditionally on above given condition. Please help…

Answer

You can use CTEsto get the result.

WITH Result AS(
    SELECT *, COUNT(1) OVER (ORDER BY Id) AS AllTasksCount
     FROM User U
    JOIN TASK T ON U.Id = T.UserId
),PendingTasks AS (
    SELECT [NAme], COUNT(1) AS AllPendingTaskCount FROM Result
    WHERE Completed IS NULL and Exception IS NULL
    GROUP BY [Name]
),CompletedTasks AS (
    SELECT [NAme], COUNT(1) AS AllCompletedTaskCount FROM Result
    WHERE Completed IS NOT NULL and Exception IS NULL
    GROUP BY [Name]
),ExceptionTasks AS (
    SELECT [NAme], COUNT(1) AS AllExecptionTaskCount FROM Result
    WHERE Completed IS NULL and Exception IS NOT NULL
    GROUP BY [Name]
)
SELECT DISTINCT R.[Name]
      ,AllTasksCount
      ,AllPendingTaskCount
      ,AllCompletedTaskCount
      ,AllExecptionTaskCount
FROM Result R
JOIN PendingTasks PT 
  ON PT.[Name] = R.[Name]
JOIN CompletedTasks CT 
  ON CT.[Name] = R.[Name]
JOIN ExceptionTasks ET 
  ON ET.[Name] = R.[Name]

Added sample schema and data. Please update it as per your requirements. SQLFiddle