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
  • 13,158
  • 4
  • 29
  • 44
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,886
  • 6
  • 61
  • 78
MT0
  • 143,790
  • 11
  • 59
  • 117
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
  • 46,766
  • 14
  • 87
  • 128
  • `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,646
  • 1
  • 19
  • 33