sql query to get employee sum and employee count

SELECT   person_number,taxable_earn, Basic_Life,Tax_Units,Basic_Spouse,age_depnt,age_EE
      FROM   (SELECT     
                         pra.result_value , 
                         pra.elementname, 
                         prd.person_number ,
                         pap.date_of_birth age_depnt,
                         pra.date_of_birth age_EE
                FROM     pay_pay_relationships_dn prd 
               INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id 
               INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id 
               INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id 
               WHERE ppa.payroll_id = pap.payroll_id
                 AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
     PIVOT    (SUM(result_value )
               FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units,'Basic_Spouse', Basic_Spouse))
       

The above query gives me the data like

   person_number            taxable_earn              Basic_Life             Tax_Units      Basic_Spouse     depent_dob   age_EE
   10                           78.9                                            10.5                                        39
   20                           76.7                    10.2                                12.3                32          21
   30                            2.3                    10.3                    8.9         19.0                34          25
   40                                                   14.3                    8.9         19.0                52          67
   
       

I want the count of employees and total of each of the above columns in the below format –

            Basic_Life      taxable_earn    Basic_Spouse
Total        34.8               157.9           50.3
EMP_count    3                      3           3       

            

what function should I use for this ?

Answer

WITH pivot_subquery as 
(
SELECT   person_number,taxable_earn, Basic_Life,Tax_Units,Basic_Spouse,age_depnt,age_EE
      FROM   (SELECT     
                         pra.result_value , 
                         pra.elementname, 
                         prd.person_number ,
                         pap.date_of_birth age_depnt,
                         pra.date_of_birth age_EE
                FROM     pay_pay_relationships_dn prd 
               INNER JOIN pay_payroll_rel_actions pra ON prd.payroll_relationship_id = pra.payroll_relationship_id 
               INNER JOIN pay_payroll_actions ppa ON pra.payroll_action_id = ppa.payroll_action_id 
               INNER JOIN pay_all_payrolls_f pap ON ppa.element_type_id = pap.element_type_id 
               WHERE ppa.payroll_id = pap.payroll_id
                 AND ppa.effective_date BETWEEN :p_start_date AND :p_end_date)
     PIVOT    (SUM(result_value )
               FOR elementname IN ('taxable_earn' taxable_earn, 'Basic_Life' Basic_Life, 'Tax_Units' Tax_Units,'Basic_Spouse', Basic_Spouse))
)
select 'Total' as row_desc
       ,sum(nvl(Basic_Life,0.0)) as Basic_Life
       ,sum(nvl(taxable_earn,0.0)) as taxable_earn
       ,sum(nvl(Basic_Spouse,0.0)) as Basic_Spouse
 from pivot_subquery 
union all
select 'EMP_count' as row_desc
   ,nvl((select count(*) from pivot_subquery where Basic_Life is not null),0) as Basic_Life
  ,nvl((select count(*) from pivot_subquery where taxable_earnis not null),0) as taxable_earn
  ,nvl((select count(*) from pivot_subquery where Basic_Spouse is not null),0) as Basic_Spouse
 from dual