DB2 SUM of MAX Values – How to Calculate in a Single Table

db2db2-9.7maxsum

I have looked through many of the questions asked and answered but can't seem to find my answer, even kludging the provided answers into my queries – most usually fail, the remainder provide garbage results.

NOTE THAT THIS IS FOR IBM DB2 v9.7! NOT SQL Server or anything newer.

I have a single table that contains quasi-distinct rows, but has no key. The odds of finding a duplicate are next to none based on ALL the fields, and the probability of duplication increases with the fewer fields selected in the query. I'm mostly ok with that at this point. There are more fields than listed below, and I have added a key just for reference…

PSEUDO-QUERY

select sum(max(NumImages)) from table where Source in ('A','B') and Event='190'

TABLE

Key   ID    NumImages   Source    Event
1     1     4           A         190
2     1     -           -         247
3     1     2           B         190
4     1     -           -         134

5     2     7           C         190
6     2     2           A         190
7     2     1           B         190
8     2     5           A         190

9     3     5           A         190
10    3     -           -         247
11    3     -           -         134
12    3     -           -         815

13    4     2           A         190
14    4     9           A         247
15    4     5           B         190
16    4     4           B         134

PARAMETERS

Source in ('A','B') 
Event = '190'
NumImages is not null

(*I don't think that will ever happen with the preceding parameters)

OUTPUT

ID    Sum
1     6
2     6
3     5
4     7

Because:

  • 1 = 6 ; keys 1 and 3 are both max for event 190 and source A or B
  • 2 = 6 ; key 8 (5 – max for Event 190 Source A) + key 7 (1 – max for Event 190 Source B). Source C (key 5) is ignored.
  • 3 = 5 ; key 9 is the only value matching the parameters
  • 4 = 7 ; keys 13 and 15 are the only ones that match Event 190, even though Source and NumImages have values that match against the other parameters.

Best Answer

I think you want multiple aggregations:

First find the max for each ID-Source combination, then sum for every ID:

select ID, sum(MaxNumImages) as SumMaxNumImages
from
  ( 
    select ID, max(NumImages) as MaxNumImages
    from table 
    where Source in ('A','B') and Event = '190'
      and NumImages is not null
    group by ID, Source
  ) 
  as agg
group by ID ;
Related Question