Get total count for the multiple select queries and return as single table-CodePudding

I want to get total of all the below four counts of the separate SQL queries and return as single table as below:

select count(*) OpenTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 1     
select count(*) AnsweredTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 0     
select count(*) CandidateTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE())    
select count(*) AlertTaskCount from [COM].[USER_GENERATED_TASK_LIST] where TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE())

Is there any easy method to get the total task from the above select queries and then return as single row.

CodePudding user response：

You can use conditional aggregation, such as:

select
    Sum(case when ACTIVE_FLAG = 1 then 1 else 0 end) OpenTaskCount,
    Sum(case when ACTIVE_FLAG = 0 then 1 else 0 end) AnsweredTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) then 1 else 0 end) CandidateTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) then 1 else 0 end) AlertTaskCount
from COM.USER_GENERATED_TASK_LIST
where TASK_STATUS = 'Active';

Edit - to add a total column I'd probably just use a derived table, like:

select OpenTaskCount, AnsweredTaskCount, CandidateTaskCount, AlertTaskCount,
  OpenTaskCount   AnsweredTaskCount   CandidateTaskCount   AlertTaskCount as TotalTask
from (
  select 
    Sum(case when ACTIVE_FLAG = 1 then 1 else 0 end) OpenTaskCount,
    Sum(case when ACTIVE_FLAG = 0 then 1 else 0 end) AnsweredTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) then 1 else 0 end) CandidateTaskCount,
    Sum(case when ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) then 1 else 0 end) AlertTaskCount
  from COM.USER_GENERATED_TASK_LIST
  where TASK_STATUS = 'Active'
)t;

CodePudding user response：

You may try with conditional aggregation and the appropriate CASE expressions (the NULL values are eliminated by an aggregate):

SELECT 
   OpenTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' AND ACTIVE_FLAG = 1 THEN 1 END),
   AnsweredTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' and ACTIVE_FLAG = 0 THEN 1 END),
   CandidateTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE()) THEN 1 END),
   AlertTaskCount = COUNT(CASE WHEN TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()) THEN 1 END)
   TotalTask = COUNT(CASE WHEN
     (TASK_STATUS = 'Active' AND ACTIVE_FLAG = 1) OR        
     (TASK_STATUS = 'Active' and ACTIVE_FLAG = 0) OR
     (TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM >= DATEADD(day, -90, GETDATE())) OR
     (TASK_STATUS = 'Active' and ACTIVE_FLAG = 1 and TASK_ASSIGNED_DTM <= DATEADD(day, -90, GETDATE()))
                     THEN 1 END)
FROM [COM].[USER_GENERATED_TASK_LIST]