CODEWITHSUNDEEP
oracle-databasesumcase
SYMA
SYMA

Reputation: 119

Sum function in Case statement

I am working on a query where I need to find sum in various condition.Right now my query is:

select
sum(case when condition then A_amount end) as S_A_amount,
sum(case when condition then B_amount end) as S_B_amount

but in third condition I need to use sum function in When condition also

sum(case when sum(C_amount)>3 then 6 else sum(C_amount) end) as S_C_amount
from table A group by item

I am unable to implement the third condition. I am getting not a single group-by error.

Upvotes: 0

Views: 5431

Answers (4)

Thomas
Thomas

Reputation: 366

I would do something like this:

SELECT item,
       sum(case when condition then A_amount end) as S_A_amount,
       sum(case when condition then B_amount end) as S_B_amount,
       sum(case when s_c_amount>3 then 6 else s_c_amount end) as S_C_amount
 FROM (select item,
              A_amount,
              B_amount,
              sum(C_amount) over (partition by item) as s_c_amount
         from table A)
GROUP BY item

Upvotes: -1

MT0
MT0

Reputation: 168741

Without some sample data it is hard to determine what you are trying to achieve but using SUM(SUM(value)) within the same group is not going to give a different result to just using SUM(value) so it appears you could use:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE A ( item, A_Amount, B_Amount, C_Amount, cond ) AS
SELECT 1, 1, 1, 1, 1 FROM DUAL UNION ALL
SELECT 1, 1, 1, 1, 2 FROM DUAL UNION ALL
SELECT 1, 1, 1, 1, 3 FROM DUAL UNION ALL
SELECT 1, 1, 1, 0, 4 FROM DUAL UNION ALL
SELECT 2, 2, 2, 2, 1 FROM DUAL UNION ALL
SELECT 2, 2, 2, 2, 2 FROM DUAL UNION ALL
SELECT 2, 2, 2, 2, 3 FROM DUAL UNION ALL
SELECT 2, 2, 2, 2, 4 FROM DUAL;

Query 1:

SELECT item,
       SUM( CASE WHEN cond > 0 THEN A_amount END ) AS S_A_amount,
       SUM( CASE WHEN cond > 0 THEN B_amount END ) AS S_B_amount,
       CASE WHEN SUM(C_amount)>3 THEN 6 ELSE SUM(C_amount) END AS S_C_amount
FROM   A
GROUP BY item

Results:

| ITEM | S_A_AMOUNT | S_B_AMOUNT | S_C_AMOUNT |
|------|------------|------------|------------|
|    1 |          4 |          4 |          3 |
|    2 |          8 |          8 |          6 |

Upvotes: 3

Paul Maxwell
Paul Maxwell

Reputation: 35623

Move the existing query into a subquery and place the case expression logic in the outer query layer e.g.

select
       d.*
     , case when S_C_amount > 3 then 6 else S_C_amount end
from (
      select
            item
          , sum(case when condition then A_amount end) as S_A_amount
          , sum(case when condition then B_amount end) as S_B_amount
          , sum(case when condition then C_amount end) as S_C_amount
      from t
      group by item
      ) d

Upvotes: 0

Rene
Rene

Reputation: 10551

Is this what you need?

select case
          when sum_c_amount > 3 then
           6
          else
           sum_c_amount
       end as s_c_amount
  from (select sum(c_amount) as sum_c_amount
          from table a
         group by item)

Upvotes: 0

Related Questions