Oracle SQL sum up columns resulted from aggregate function and decode-CodePudding

i have 4 columns resulted from different conditions decode and aggregate function MAX

SELECT MAX(DECODE(pett.element_name,'Basic Salary',peevf.screen_entry_value,0)) Salary,
MAX(DECODE(pett.element_name,'Transportation Allowance',peevf.screen_entry_value,0)) Transportation,
MAX(DECODE(pett.element_name,'Mobile Allowance',peevf.screen_entry_value,0)) Mobile,
MAX(DECODE(pett.element_name,'Housing Allowance',peevf.screen_entry_value,0)) Housing,

i want to make another column that sums up these values as net salary like this:

Salary   Mobile   Housing   Transportation AS "Net Salary"

but it doesn't seem to accept aliases, how can I achieve that? thanks in advance

CodePudding user response：

You can summarize all of them as formulas:

SELECT MAX(DECODE(pett.element_name,'Basic Salary',peevf.screen_entry_value,0)) Salary,
       MAX(DECODE(pett.element_name,'Transportation Allowance',peevf.screen_entry_value,0)) Transportation,
       MAX(DECODE(pett.element_name,'Mobile Allowance',peevf.screen_entry_value,0)) Mobile,
       MAX(DECODE(pett.element_name,'Housing Allowance',peevf.screen_entry_value,0)) Housing,
       MAX(DECODE(pett.element_name,'Basic Salary',peevf.screen_entry_value,0)) 
        
       MAX(DECODE(pett.element_name,'Transportation Allowance',peevf.screen_entry_value,0))
        
       MAX(DECODE(pett.element_name,'Mobile Allowance',peevf.screen_entry_value,0))
        
       MAX(DECODE(pett.element_name,'Housing Allowance',peevf.screen_entry_value,0)) 
       AS "Net Salary"
FROM Table1

OR use a CTE to utilize aliases:

WITH max_table AS
(
SELECT MAX(DECODE(pett.element_name,'Basic Salary',peevf.screen_entry_value,0)) Salary,
       MAX(DECODE(pett.element_name,'Transportation Allowance',peevf.screen_entry_value,0)) Transportation,
       MAX(DECODE(pett.element_name,'Mobile Allowance',peevf.screen_entry_value,0)) Mobile,
       MAX(DECODE(pett.element_name,'Housing Allowance',peevf.screen_entry_value,0)) Housing
FROM Table1
)

SELECT Salary,
       Transportation,
       Mobile,
       Housing,
       Salary   Transportation   Mobile   Housing AS "Net Salary"
FROM max_table

CodePudding user response：

If there is only a single row for each element name and you are trying to pivot rows to columns then you can use:

SELECT MAX(
         CASE
         WHEN pett.element_name = 'Basic Salary'
         THEN peevf.screen_entry_value
         ELSE 0
         END
       ) AS Salary,
       MAX(
         CASE 
         WHEN pett.element_name = 'Transportation Allowance'
         THEN peevf.screen_entry_value
         ELSE 0
         END
       ) AS Transportation,
       MAX(
         CASE
         WHEN pett.element_name = 'Mobile Allowance'
         THEN peevf.screen_entry_value
         ELSE 0
         END
       ) AS Mobile,
       MAX(
         CASE
         WHEN pett.element_name = 'Housing Allowance'
         THEN peevf.screen_entry_value
         ELSE 0
         END
       ) AS Housing,
       SUM(
         CASE
         WHEN pett.element_name IN (
                'Basic Salary', 'Transportation Allowance',
                'Mobile Allowance', 'Housing Allowance'
              )
         THEN peevf.screen_entry_value
         ELSE 0
         END
       ) AS net_salary,

Or:

SELECT COALESCE(salary, 0) AS salary,
       COALESCE(transport, 0) AS transport,
       COALESCE(mobile, 0) AS mobile,
       COALESCE(housing, 0) AS housing,
       COALESCE(salary, 0)   COALESCE(transport, 0)
          COALESCE(mobile, 0)   COALESCE(housing, 0) AS net_salary
FROM   ...
PIVOT (
  MAX(peevf.screen_entry_value)
  FOR pett.element_name IN (
    'Basic Salary' AS salary,
    'Transportation Allowance' AS transportation,
    'Mobile Allowance' AS mobile,
    'Housing Allowance' AS housing
  )
)