CASE Statement inside aggregate function use temp segment and might cause the ORA-01652 problem

Reference:
TechOnTheNet.com ORA-01652: unable to extend temp segment by num in tablespace name

After the topic「PLSQL case when sum count ORA-00934: group function is not allowed here」, the ORA-01652 error happened，it let me know that use CASE statement inside the aggregate function would create temp segment and might cause that problem.

Situation:

select sum(case
             when ... then
              1
             else
              0
           end) "cnt1",
       sum(case
             when ... then
              1
             else
              0
           end) "cnt2"
  from some_table

Solution: Don’t use that while the selected tuples are not small, e.g.

select a.key1, a.key2, a.cnt1, b.cnt2
  from (select key1, key2, count(*) as cnt1
          from some_table
         where ... -- condition for counting cnt1
         group by key1, key2) a
  left outer join (select key1, key2, count(*) as cnt2
                     from some_table
                    where ... -- condition for counting cnt2
                    group by key1, key2) b on b.key1 = a.key1
                                          and b.key2 = a.key2