title: Sum all produced value of select query with union all and distinct using MS sql server
body: <p>I have produced data from a query having a distinct value of a date. Now what I want is to get the sum of the total days and put on the last row that is why I used a <code>UNION ALL</code>. The problem is how can I get the total sum of the days produced only by the query.</p>
  
  <p>below is the query:</p>
  
  <pre><code>USE  ANR_Payroll
  Select 
  Distinct a.[from],
  (Select top 1 [to] FROM [ANR_Payroll].[dbo].[tblPayrollTransactionEntry] Z where A.[From] =Z.[From] and A.employeecode = Z.EmployeeCode ) As 'TO',
  '***' as 'Total Days Worked',
  (Select top 1 Employeecode FROM [ANR_Payroll].[dbo].    [tblPayrollTransactionEntry] Z where A.[from] =Z.[from] and A.employeecode =     Z.EmployeeCode ) As 'Employeecode',
  B.LastName,
  B.FirstName,
  B.Middlename,
  B.Levelcode,
  (Select top 1 z.[BatchCode] FROM [ANR_Payroll].[dbo].    [tblPayrollTransactionEntry] Z where A.[from] =Z.[from] and A.employeecode =     Z.EmployeeCode ) As 'BatchCode' ,
  (Select top 1 Z.EarningCode FROM [ANR_Payroll].[dbo].    [tblPayrollTransactionEntry] Z where A.[from] =Z.[From] and A.employeecode = Z.EmployeeCode ) As 'Earning Code' ,
  (Select top 1 Z.Rate FROM [ANR_Payroll].[dbo].[tblPayrollTransactionEntry] Z     where A.[from] =Z.[From] and A.employeecode = Z.EmployeeCode ) As 'Rate',
  (Select top 1 Z.Ratetype FROM [ANR_Payroll].[dbo].    [tblPayrollTransactionEntry] Z where A.[from] =Z.[From] and A.employeecode =     Z.EmployeeCode ) As 'RateType',
  (Select top 1 A.[noofunits] / 8 FROM [ANR_Payroll].[dbo].    [tblPayrollTransactionEntry] Z where A.[from] =Z.[From] and A.employeecode =     Z.EmployeeCode ) as 'DAYS',
  '' Tenure,
  'A' Orderkey 
  from 
     tblPayrolltransactionEntry as A 
     inner join 
     tblEmployeeMainInfo as B ON a.EmployeeCode = b.EmployeeCode 
    where B.Employeecode like '%0000011350%'and EarningCode = 'SF' and PayType     = 'PayOut'  
     UNION ALL 
   Select
      CASE when '' = '1900-01-01 00:00:00' THEN '**' END,
  CASE when '' = '1900-01-01 00:00:00' THEN '**' END, 
  'Total Days Worked', 
  A.Employeecode,
  '',
  '',
  '',
  '',
  '',
  '',
  '',
  '',
  (SUM(NoOfUNits) / 8) as 'DAYS',
  ((SUM(NoOfUNits) / 8) / 366) as 'Tenure',     
  'B' Orderkey
  from tblPayrolltransactionEntry as A 
  inner join tblEmployeeMainInfo as B ON a.EmployeeCode = b.EmployeeCode 
  where B.Employeecode like '%0000011350%'and EarningCode = 'SF' and PayType =     'PayOut'  
      Group by a.employeecode 
   ORDER by [Employeecode],Orderkey
  </code></pre>
  
  <hr>
  
  <p>Attached file is my output using that query.</p>
  
  <p>The total is get from all the value without the using distinct
  <a href="https://i.stack.imgur.com/dmlzu.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dmlzu.png" alt="enter image description here"></a></p>