Agfdhyk

This is the table I have.

TASK_CD    |  STATUS | DUE_DATE

----------------------------------

T0001      |  NW     | SYSDATE + 1

T0001      |  IP     | SYSDATE 

T0001      |  AG     | SYSDATE

T0002      |  NW     | SYSDATE - 1

T0002      |  NW     | SYSDATE - 2

T0003      |  AG     | SYSDATE + 1

This is the query I have right now

select TASK_CD, STATUS, SUM(1) TOTALS, 

SUM(CASE WHEN (TRUNC(DUE_DATE ) > SYSDATE) then 1 else 0 end) FUTURE, 

SUM(CASE WHEN (TRUNC(DUE_DATE ) = SYSDATE) then 1 else 0 end) TODAY, 

SUM(CASE WHEN (TRUNC(DUE_DATE ) < SYSDATE) then 1 else 0 end) OVERDUE, 

FROM TM_TASK GROUP BY TASK_CD, STATUS;

result will be

TASK_CD | STATUS | TOTALS | FUTURE | TODAY | OVERDUE

----------------------------------------------------

T0001   | NW     | 1      | 1      | 0     | 0

T0001   | IP     | 1      | 0      | 1     | 0

T0001   | AG     | 1      | 0      | 1     | 0

T0002   | NW     | 2      | 0      | 0     | 2

T0003   | AG     | 1      | 1      | 0     | 0

By removing STATUS from select and group, I get

TASK_CD | TOTALS | FUTURE | TODAY | OVERDUE

-----------------------------------------------

T0001   | 3      | 1      | 2     | 0

T0002   | 2      | 0      | 0     | 2

T0003   | 1      | 1      | 0     | 0

Is there a way to combine both and combine the results, giving me TOTALS_ALL, FUTURE_TOTALS, OVERDUE_TOTALS, TODAY_TOTALS where these are the sums when status is not considered for the GROUP BY

TASK_CD | STATUS | TOTALS | FUTURE | TODAY | OVERDUE| TOTALS_ALL | FUTURE_TOTAL | TODAY_TOTAL | OVERDUE_TOTAL

T0001   | NW     | 1      | 1      | 0     | 0      | 3          | 1

             | 2           | 0

T0001   | IP     | 1      | 0      | 1     | 0      | 3          | 1

             | 2           | 0

T0001   | AG     | 1      | 0      | 1     | 0      | 3          | 1

             | 2           | 0

T0002   | NW     | 2      | 0      | 0     | 2      | 2          | 0

             | 0           | 2

T0003   | AG     | 1      | 1      | 0     | 0      | 1          | 1

             | 0           | 0

You can use the subquery factoring clause (more commonly called a common table expresssion) to generate your initial data set and then an analytic SUM() to aggregate

with initial_totals as (

select task_cd

     , status

     , count(*) as totals

     , sum(case when due_date >= trunc(sysdate) + 1 then 1 else 0 end) as future

     , sum(case when trunc(due_date) = trunc(sysdate) then 1 else 0 end) as today

     , sum(case when due_date < trunc(sysdate) then 1 else 0 end) as overdue

  from tm_task

 group by task_cd, status

       )

select task_cd

     , status

     , future

     , today

     , overdue

     , sum(future) over (partition by task_cd) as future_total

     , sum(today) over (partition by task_cd) as today_total

     , sum(overdue) over (partition by task_cd) as overdue_total

  from initial_totals

This is your original query (modified slightly see below) and then an analytic sum over each task code. An analytic sum is the same as an aggregate, but without putting the result against each row.

You can see this in action on db<>fiddle.

You'll note I've changed your date logic. This is because TRUNC(DUE_DATE ) > SYSDATE is the same as saying when midnight today is greater than this second. I've changed this to due_date >= trunc(sysdate) + 1, which is when this second is at or after midnight tomorrow. I've changed all the date logic in a similar manner.

You would use window functions:

select TASK_CD, STATUS, SUM(1) TOTALS, 

       SUM(CASE WHEN (TRUNC(DUE_DATE ) > SYSDATE) then 1 else 0 end) as FUTURE, 

       SUM(CASE WHEN (TRUNC(DUE_DATE ) = SYSDATE) then 1 else 0 end) as TODAY, 

       SUM(CASE WHEN (TRUNC(DUE_DATE ) < SYSDATE) then 1 else 0 end) as OVERDUE, 

       SUM(SUM(CASE WHEN (TRUNC(DUE_DATE ) > SYSDATE) then 1 else 0 end)) OVER (PARTITION BY status) as TOTAL_FUTURE, 

       SUM(SUM(CASE WHEN (TRUNC(DUE_DATE ) = SYSDATE) then 1 else 0 end)) OVER (PARTITION BY status) as TOTAL_TODAY, 

       SUM(SUM(CASE WHEN (TRUNC(DUE_DATE ) < SYSDATE) then 1 else 0 end)) OVER (PARTITION BY status) as TOTAL_OVERDUE

FROM TM_TASK

GROUP BY TASK_CD, STATUS;