Using OLAP-specs to compute selective running totals

Question

Let's say I have a table with columns id, subid, state, value, where (id, subid) is a unique tuple, state may be 0 or some value <> 0, and value is just some number. I want to build a query that gives columns id, sum(value) over all entries with this id, and sum(value) over all such rows having state <> 0.

Of course, I know how to build this using subselects to compute the sums. However, I wonder how to do this elegantly using some OLAP-specification. Since I am not yet used to OLAP-specifications, I am not very certain how to do this.

Surely, something like

select * from (
  select 
    id,
    state,
    sum(value) over (partition by decode(state, 0, 0, 1),
    sum(value) over ()
  from table
)
where state=0

would do the job. My questions:

• Is there a better way to fetch the desired result?
• Does this query perform better than the classical subselect-solution at all?

(I am using DB2 z/OS. Unfortunately, I do not have permissions to use explain on this installation)

Edit: Sample data and expected results: Consider the table

id      subid       state       value
-------------------------------------
 1          1           0           1
 1          1           0           2
 1          2           1           4
 2          1           0           8

The expected result would be a table which, for each ID, sums the values of all entries and the values of all entries where state=0.

 id     sum w/ state=0      total sum
 ------------------------------------
 1                   3              7
 2                   8              8

Answer 1

Given your sample data, I don't think it is necessary to use OLAP functions. Something like:

select id, sum(case when state = 0 then value end) as sum_w_s_0
     , sum(value) as total_sum
from t
group by id

But in the subject, you mention running totals, which usually means a cumulative sum of some kind. Can you add more sample data in case this answer does not achieve what you want?