0

I have salary table and attribute which employer get extra bonus (yes value). I want to calculate sum(salary) of employer without exta-bonus and sum of salary with bonus. Table with attribute is 

id   salary     extra_bonus
---  -------    -----------
 1   1200       yes
 2   900
 3   1300
 4   800        yes
 5   1100       yes

I want to get report like

sumsalary  sumextrabonussalary  percent
---------  -------------------  -------
3100       2100                 67,7%
Serkan Arslan
  • 12,740
  • 4
  • 28
  • 42
user3275693
  • 11
  • 1
  • Check out GROUP BY, and aggregate functions. – jarlh Oct 10 '17 at 12:35
  • 3
    Did you do your math right in your results? Several things don't look right (such as the numbers possible being reversed and the 2100 should be 2200 maybe)? – Tom H Oct 10 '17 at 12:36

3 Answers3

2
SELECT SUM( CASE when extra_bonus = 'yes' THEN NULL ELSE salary END ) AS sumsalary,
       SUM( CASE when extra_bonus = 'yes' THEN salary END ) AS sumextrabonussalary,
       SUM( CASE when extra_bonus = 'yes' THEN NULL ELSE salary END )
       / SUM( CASE when extra_bonus = 'yes' THEN salary END )
       * 100 AS percent
FROM   your_table;
Nick Krasnov
  • 26,363
  • 6
  • 58
  • 76
MT0
  • 113,669
  • 10
  • 50
  • 103
0

It's been a long time since I've worked with Oracle, but I believe that this should work:

SELECT
    SUM(CASE WHEN extra_bonus = 'yes' THEN 0 ELSE salary END) As sumsalary,
    SUM(CASE WHEN extra_bonus = 'yes' THEN salary ELSE 0 END) AS sumextrabonussalary,
    SUM(CASE WHEN extra_bonus = 'yes' THEN salary ELSE 0 END)/
    SUM(CASE WHEN extra_bonus = 'yes' THEN 0 ELSE salary END) AS percent
FROM
    My_Table
Tom H
  • 45,807
  • 14
  • 84
  • 124
  • `NULL <> 'yes'` does not evaluate to true - it evaluates to `NULL`; so your `sumsalary` value will always be 0. – MT0 Oct 10 '17 at 12:45
  • It only evaluates to NULL if it has a value of NULL. If it has a value of empty string (which is what it looks like to me in the OP) then it will evaluate to true or false appropriately. You are correct that I should have pointed that out though since the OP is a bit unclear. – Tom H Oct 10 '17 at 12:50
  • [Oracle treats empty strings as `NULL`](https://stackoverflow.com/q/203493/1509264). – MT0 Oct 10 '17 at 12:52
  • Thanks. I wasn't aware of that. Silly database engine. – Tom H Oct 10 '17 at 13:12
0

Use conditional aggregation and subquery behind FROM

 SELECT sumsalary, sumextrabonussalary, sumsalary / sumextrabonussalary * 100 as Percent
 FROM
 (
   SELECT SUM( CASE WHEN extra_bonus IS NULL THEN salary END ) AS sumsalary,
          SUM( CASE WHEN extra_bonus = 'yes' THEN salary END ) AS sumextrabonussalary
   FROM yourtable
 ) t
Radim Bača
  • 10,526
  • 1
  • 18
  • 32