Trying to perform sum of count using group by and union

Question

I am trying to sum the counts of three queries using union. These queries also contain group by clauses within them. Below is my query that I have written:

select 
       extract(year from start_date), 
       extract(month from start_date),
       APPLICATION_TYPE,
       sum(TOTAL) as Overall_TOTAL
from (
select 
       extract(year from A.start_date) as Start_Year, 
       extract(month from A.start_date) as Start_Month,
       A.APPLICATION_TYPE as Type,
       count(A.TRANSACTION_NUMBER) as Total
from lnr_application A
where 
      A.START_DATE >= to_date('&sdate','DD/MM/YYYY')
      and A.START_DATE <= to_date('&edate','DD/MM/YYYY')  
      and A.permission_type = 'HRW'
      and A.status_cd in ('AP')
group by extract(year from start_date), extract(month from start_date), A.APPLICATION_TYPE
union all
select 
       extract (year from A.tstamp) as Start_Year, 
       extract (month from A.tstamp) as Start_Month, 
       A.application_type as Type,
       count(A.transaction_number) as Total
from lnr_application A
where 
      A.permission_type = 'HRW'
      and A.status_cd in ('RF')
      and trunc(A.tstamp) >= to_date ('&sdate','dd/mm/yyyy')
      and trunc(A.tstamp) <= to_date ('&edate','dd/mm/yy')
group by extract (year from A.tstamp), extract (month from A.tstamp), A.application_type 
union all
select 
       extract (year from A.tstamp) as Start_Year, 
       extract (month from A.tstamp) as Start_Month, 
       A.application_type as Type, 
       count(A.transaction_number) as Total
from lnr_application A
where 
      A.permission_type = 'HRW'
      and A.status_cd in ('CL')
      and trunc(A.tstamp) >= to_date ('&sdate','dd/mm/yyyy')
      and trunc(A.tstamp) <= to_date ('&edate','dd/mm/yy')      
group by extract (year from A.tstamp), extract (month from A.tstamp), A.application_type
) tmp
group by extract(year from start_date), extract(month from start_date), APPLICATION_TYPE
order by extract(year from start_date), extract(month from start_date), APPLICATION_TYPE

When I execute the query, I get an error message that Start_Date is an invalid identifier. If I remove the sum component from the top i.e. just union all three queries, I get the below result:

2011    7   A   627 
2011    7   A   21 
2011    7   A   1 
2011    7   C   1585 
2011    7   C   1    
2011    7   I   1 
2011    7   I   154 
2011    7   I   3

I want to total the sum of the counts for the respective year, month and application type, as below:

2011    7   A   649
2011    7   C   1586 
2011    7   I   158

Can someone please help me?

Answer 1

You can combine the three union all subqueries into a single query, and then you don't need the extra inline view, leading to this simple query:

select extract(year from a.start_date)
     , extract(month from a.start_date)
     , a.application_type
     , sum(a.total) as overall_total
  from lnr_application a
 where a.permission_type = 'HRW'
   and a.status_cd in ('AP','RF','CL')
   and a.tstamp between to_date('&sdate','dd/mm/yyyy') and to_date('&edate','dd/mm/yyyy') + interval '1' day - interval '1' second
 group by extract(year from a.start_date)
     , extract(month from a.start_date)
     , a.application_type
 order by extract(year from a.start_date)
     , extract(month from a.start_date)
     , a.application_type

And simplifying some more, by using trunc(...,'mm') instead of two times extract:

select extract(year from trunc(a.start_date,'mm'))
     , extract(month from trunc(a.start_date,'mm'))
     , a.application_type
     , sum(a.total) as overall_total
  from lnr_application a
 where a.permission_type = 'HRW'
   and a.status_cd in ('AP','RF','CL')
   and a.tstamp between to_date('&sdate','dd/mm/yyyy') and to_date('&sdate','dd/mm/yyyy') + interval '1' day - interval '1' second
 group by trunc(a.start_date,'mm')
     , a.application_type
 order by trunc(a.start_date,'mm')
     , a.application_type

Regards,
Rob.

Answer 2

select 
       Start_Year, 
       Start_Month,
       APPLICATION_TYPE,
       sum(TOTAL) as Overall_TOTAL
from (
select 
       extract(year from A.start_date) as Start_Year, 
       extract(month from A.start_date) as Start_Month,
       A.APPLICATION_TYPE as Type,
       count(A.TRANSACTION_NUMBER) as Total
from lnr_application A
where 
      A.START_DATE >= to_date('&sdate','DD/MM/YYYY')
      and A.START_DATE <= to_date('&edate','DD/MM/YYYY')  
      and A.permission_type = 'HRW'
      and A.status_cd in ('AP')
group by extract(year from start_date), extract(month from start_date), A.APPLICATION_TYPE
union all
select 
       extract (year from A.tstamp) as Start_Year, 
       extract (month from A.tstamp) as Start_Month, 
       A.application_type as Type,
       count(A.transaction_number) as Total
from lnr_application A
where 
      A.permission_type = 'HRW'
      and A.status_cd in ('RF')
      and trunc(A.tstamp) >= to_date ('&sdate','dd/mm/yyyy')
      and trunc(A.tstamp) <= to_date ('&edate','dd/mm/yy')
group by extract (year from A.tstamp), extract (month from A.tstamp), A.application_type 
union all
select 
       extract (year from A.tstamp) as Start_Year, 
       extract (month from A.tstamp) as Start_Month, 
       A.application_type as Type, 
       count(A.transaction_number) as Total
from lnr_application A
where 
      A.permission_type = 'HRW'
      and A.status_cd in ('CL')
      and trunc(A.tstamp) >= to_date ('&sdate','dd/mm/yyyy')
      and trunc(A.tstamp) <= to_date ('&edate','dd/mm/yy')      
group by extract (year from A.tstamp), extract (month from A.tstamp), A.application_type
) tmp
group by Start_Year, 
       Start_Month,
       APPLICATION_TYPE
order by Start_Year, 
       Start_Month,
       APPLICATION_TYPE

Answer 3

In the SELECT statement of the union, you need to use Start_Year and Start_Month instead of the EXTRACT statements. Also, use Type instead of Application_Type.