Multiple aggregate function

Question

I have the following script -

select sum(duration) as duration from opr 
       where op in ('GRC','GCQ')
       and timestamp between '20130101000000' and '20130930235959'

I receive the value - 34459298 seconds. I would like to include these restrictions - The duration which is

<= '18000000' (seconds) should be multiplied to 0.14
The duration between > '18000000' and <= '27000000' should be multiplied to 0.11
and the duration > '27000000' should be multiplied to 0.09

I have tried with this case statement -

   case when duration <= '18000000' 
        then (duration)*0.14
        when duration > '18000000' and duration <= '27000000'
        then (duration)*0.11
        when duration > '27000000' 
        then (duration)*0.09
          else 0 end as bal

However, I receive this value 34459298 multiplied to 0.09, because it is bigger then '27000000', which is not the idea. The idea is all the seconds which these two op ('GRC','GCQ') had made to be multiplied with the above values.

Could you please help me to do this?

Przemyslaw Kruglej · Accepted Answer · 2013-10-28 10:19:35Z

3

Is duration a number or a string? Would be better to compare with numbers, I guess:

SELECT
    SUM(
      CASE
        WHEN duration <= 18000000 THEN duration * 0.14
        WHEN duration > 18000000 AND duration <= 27000000 THEN duration * 0.11
        WHEN duration > 27000000 THEN duration * 0.09
        ELSE 0
      END
    ) AS duration
  FROM opr 
WHERE
  op IN ('GRC','GCQ')
  AND timestamp BETWEEN '20130101000000' AND '20130930235959'
;

answered Oct 28, 2013 at 10:19

Przemyslaw Kruglej

8,1312 gold badges30 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

baltov · Accepted Answer · 2013-10-28 10:17:08Z

1

use subquery to select specific duration and multiply duration by your values and than use sum over that subquery.

answered Oct 28, 2013 at 10:17

baltov

1945 bronze badges

Comments

Ganesh Jadhav · Accepted Answer · 2013-10-28 10:18:11Z

1

Try this:

select sum(case
        when duration <= '18000000' then (duration)*0.14
        when duration > '18000000' and duration <= '27000000' then (duration)*0.11
        when duration > '27000000' then (duration)*0.09
        else 0
    end) as bal
from opr 
where op in ('GRC','GCQ')
    and timestamp between '20130101000000' and '20130930235959'

answered Oct 28, 2013 at 10:18

Ganesh Jadhav

2,8481 gold badge22 silver badges34 bronze badges

Comments

T I · Accepted Answer · 2013-10-28 10:18:37Z

1

I think you want to do this

select sum(case when duration <= '18000000' then (duration)*0.14
                when duration > '18000000' and duration <= '27000000' then (duration)*0.11
                when duration > '27000000' then (duration)*0.09
                else 0 end as bal) as duration 
from opr 
where op in ('GRC','GCQ')
and timestamp between '20130101000000' and '20130930235959'

answered Oct 28, 2013 at 10:18

T I

9,9634 gold badges31 silver badges51 bronze badges

Collectives™ on Stack Overflow

Multiple aggregate function

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related