HOW TO USE UNION SQL TAG FOR TOTAL,PENDING

 HOW TO USE UNION SQL TAG FOR TOTAL,PENDING 

select 'CIVIL' DPT , COUNT(*) AS TOTAL ,SUM(DECODE(NVL(to_char(finalise_date,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(finalise_date,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended 

from pn_qtr_complaint where COMPLAINT_DATE BETWEEN '01-DEC-21' AND '14-DEC-21'

UNION

select 'ELECT' DPT , COUNT(*) AS TOTAL,  sum(DECODE(COMP_STATUS,'N',1,'U',1,0)) as cp_pending,sum(DECODE(COMP_STATUS,'F',1,0)) as cp_ATTENDED 

from EL_CMPLNT_HDR where COMPLAINT_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

UNION

select 'SNT' DPT , COUNT(*) AS TOTAL,SUM(DECODE(NVL(to_char(RECT_DATE,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

  SUM(DECODE(NVL(to_char(RECT_DATE,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended

  from TD_CMPL_DET where CMPL_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

  UNION

   select 'CHARMS' DPT ,COUNT(*) AS TOTAL,SUM(DECODE(NVL(to_char(CLOSE_DT,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

  SUM(DECODE(NVL(to_char(CLOSE_DT,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended

  from cm_cmplt_dtl where CMPLT_RECV_DT BETWEEN '01-DEC-21' AND '14-DEC-21';

SHOW TOTAL AT THE END OF EACH COLUMNS THEN WE USE BELOW QUERIES

select 'CIVIL' DPT , COUNT(*) AS TOTAL ,SUM(DECODE(NVL(to_char(finalise_date,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(finalise_date,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended 

from pn_qtr_complaint where COMPLAINT_DATE BETWEEN '01-DEC-21' AND '14-DEC-21'

UNION

select 'ELECT' DPT , COUNT(*) AS TOTAL,  sum(DECODE(COMP_STATUS,'N',1,'U',1,0)) as cp_pending,sum(DECODE(COMP_STATUS,'F',1,0)) as cp_ATTENDED 

from EL_CMPLNT_HDR where COMPLAINT_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

UNION

select 'SNT' DPT , COUNT(*) AS TOTAL,SUM(DECODE(NVL(to_char(RECT_DATE,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(RECT_DATE,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended

from TD_CMPL_DET where CMPL_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

  UNION

select 'CHARMS' DPT ,COUNT(*) AS TOTAL,SUM(DECODE(NVL(to_char(CLOSE_DT,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(CLOSE_DT,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended

  from cm_cmplt_dtl where CMPLT_RECV_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

  

UNION

  

  select 'TOTAL' ,SUM(TOTAL),SUM(cp_pending),SUM(cp_ATTENDED) FROM (select 'CIVIL' DPT , COUNT(*) AS TOTAL ,SUM(DECODE(NVL(to_char(finalise_date,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(finalise_date,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended 

from pn_qtr_complaint where COMPLAINT_DATE BETWEEN '01-DEC-21' AND '14-DEC-21'

UNION

select 'ELECT' DPT , COUNT(*) AS TOTAL,  sum(DECODE(COMP_STATUS,'N',1,'U',1,0)) as cp_pending,sum(DECODE(COMP_STATUS,'F',1,0)) as cp_ATTENDED 

from EL_CMPLNT_HDR where COMPLAINT_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

UNION

select 'SNT' DPT , COUNT(*) AS TOTAL,SUM(DECODE(NVL(to_char(RECT_DATE,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(RECT_DATE,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended

from TD_CMPL_DET where CMPL_DT BETWEEN '01-DEC-21' AND '14-DEC-21'

  

UNION

select 'CHARMS' DPT ,COUNT(*) AS TOTAL,SUM(DECODE(NVL(to_char(CLOSE_DT,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',1,0)) as cp_pending,

SUM(DECODE(NVL(to_char(CLOSE_DT,'DD/MM/YYYY'),'11/11/1111'),'11/11/1111',0,1)) as cp_attended

  from cm_cmplt_dtl where CMPLT_RECV_DT BETWEEN '01-DEC-21' AND '14-DEC-21');