title: Using sum inside a case or decode statement in oracle
body: <p>I tried looking for a clue but did not get a satisfying answer anywhere.</p>
  
  <p>My problem is I have a huge select statement with almost half of the columns coming out of a subquery and in these subqueries I have to check a lot of things.</p>
  
  <p>I know oracle is strict when it comes to aggregate functions but when I tried something like this in one of the subqueries, I got the ouptut as expected:</p>
  
  <pre><code>SELECT DECODE(NVL(SUM(Qty1),0),0,SUM(Qty2),SUM(Qty1))
  FROM test_v
  WHERE item_no    = 1234567;
  </code></pre>
  
  <p>But strangely when I modified it to this:</p>
  
  <pre><code>SELECT 
  DECODE(FLAG,1,SUM(Qty2),SUM(Qty1))
  FROM test_v
  WHERE item_no    = 1234567;
  </code></pre>
  
  <p>I get this error:</p>
  
  <pre><code>ORA-00937: not a single-group group function
  00937. 00000 -  "not a single-group group function"
  *Cause:    
  *Action:
  Error at Line: 9 Column: 8`
  </code></pre>
  
  <p>Why is it giving different results in two almost similar queries. I know it can be resolved by using a <code>GROUP BY</code> but how can I decide on the <code>GROUP BY</code> clause when I am not getting any other columns in the output.</p>
  
  <p>I also went on to try this with a <code>CASE</code> statement like this, but no use:</p>
  
  <pre><code>SELECT
  CASE
  WHEN flag = 1 THEN
  SUM(Qty2)
  WHEN flag = 0 THEN
  SUM(Qty1)
  END Qty
  FROM test_v
  WHERE item_no    = 1234567;
  </code></pre>
  
  <p>I still get the same error.</p>
  
  <p>I know I could override this by using another subquery for calculating the SUM but that would be like generations of subqueries living inside a select which I don't want for aesthetic, performance and personal reasons with over use of subqueries.</p>
  
  <p>Any ideas why the first one works and this doesn't??</p>