Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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.
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 |
                @Thomas Inside the CASE expression. The SUM of SUM is not going to give a different value to just using the SUM if the groups are the same in both case - as I commented at the start of the answer - so it is unclear why the OP thinks they need to SUM twice.
– MT0
                Nov 21, 2017 at 9:44
                @MT0: can you introduce one more condition in third case statement. Like Case WHEN SUM(C_amount)>3 and cond>0 then 6 else sum(C_amount) end as S_C_amount
– SYMA
                Nov 21, 2017 at 10:26
Move the existing query into a subquery and place the case expression logic in the outer query layer e.g.
select
     , case when S_C_amount > 3 then 6 else S_C_amount end
from (
      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
          , sum(case when condition then C_amount end) as S_C_amount
      from t
      group by item
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
                The sub-query will contain the SUM(C_Amount) in each row (since you are using the analytic function with OVER (PARTITION BY item)) and then summing these in the outer query you are effectively doing SELECT COUNT(1) * SUM(C_Amount) AS S_C_Amount FROM table A GROUP BY item which is not the output the OP wants. You probably want to use MAX instead of SUM in the outer query.
– MT0
                Nov 21, 2017 at 9:51
                as I said below, why would be same? if he change the value of the sum and after he sums that changed value it wont be equal... ('myitem1', 0,1,3 );('myitem1', 0,1,2 );('myitem2', 0,1,4 )--> your result is 6, my is 12
– Thomas
                Nov 21, 2017 at 10:47
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.