Oracle Sum Based On Condition

Question

SQL first so I can reference it lower:

SELECT ID, 
       SUM(length(substr(DATA_STRING, instr(DATA_STRING,'P')+1))) 
FROM DATA P, PLC PP 
WHERE P.ID = PP.ID 
  AND instr(DATA_STRING, 'F') = 0 
GROUP BY ID

The function above looks for the P in each data string and counts up how many characters are in the string.

So my problem is thus, I have two conditions that can occur, one where the data string could contain the P as in above, or when it contains a F. I need to be able to check for both and then sum up the total. The issue with the above is that I sum in a 0 if that condition when the string has an F occurs.

I almost need the equivalent of SUMIF in Excel so that I could say if when I search for P it's 0, then look for the F and add in that length and vice versa.

Please let me know if you have suggestions!

@duffymo I think its more complex than that. I could use HAVING to capture both conditoins, but the instr portion that is used in the calculation for the length would also need to change — ZAX
– ZAX, Commented Aug 11, 2014 at 17:17
Have you tried a case statement here? sum(case when condition = true then value_to_sum else 0 end)... — Twelfth
– Twelfth, Commented Aug 11, 2014 at 17:19

Gordon Linoff · Accepted Answer · 2014-08-11 17:37:54Z

4

length(substr(DATA_STRING, instr(DATA_STRING,'P')+1))? This is doing a lot of work.

From what I can tell, you want sum of the number of characters after a 'P' or 'F' in the string. If so:

SELECT ID,
       SUM(CASE WHEN data_string like '%P%'
                THEN len(DATA_STRING) - (instr(DATA_STRING, 'P') + 1) ELSE 0
           END) as Num_P,
       SUM(CASE WHEN data_string like '%F%'
                THEN len(DATA_STRING) - (instr(DATA_STRING, 'F') + 1) ELSE 0
           END) as Num_F
FROM DATA P JOIN
     PLC PP 
     ON P.ID = PP.ID 
GROUP BY ID;

I also fixed the join syntax to use explicit joins.

answered Aug 11, 2014 at 17:37

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

Collectives™ on Stack Overflow

Oracle Sum Based On Condition

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related