0

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
  • Can you add some sample data and expected result? – Lennart Feb 14 '18 at 9:48
0

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?

  • To clarify my question, I added an example. – Bubaya Feb 14 '18 at 12:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.