How do I combine two queries (union all) into one row?

Question

I have a tricky situation in trying to get information from multiple queries into a single row.

Consider the following table:

CpuUage:
    Time    time
    Group   char(10)
    Subsys  char(4)
    Jobs    int
    Cpu     int

holding the following data:

Time   Group   Subsys  Jobs  Cpu
-----  ------  ------  ----  ---
00:00  group1  NORM       1  101 (grp1-norm) A1
01:00  group1  SYS7       3  102 (grp1-sys7) A2
01:00  group1  NORM       5  104 (grp1-norm) A1
02:00  group1  NORM       7  108 (grp1-norm) A1
02:00  group2  NORM       9  116 (grp2-norm) B1
02:00  group3  SYS7      11  132 (grp3-sys7) C2
03:00  group1  SYS7      13  164 (grp1-sys7) A2
03:00  group1  IGNR      99  228 (grp1-ignr) --

The markers on the right (e.g., A1) are the sections in the report below that each row is supposed to affect.

I need a query that can return a single row for each user group but with one proviso. The values for Jobs and Cpu have to go into different columns depending on the subsystem ID and I'm only interested in the SYS7 and NORM subsystem IDs.

So, for example, we need the following (the A/B/1/2 bits are a cross-reference back to the rows above):

           <------ 1 ------>  <------ 2 ------>
   Group   NormJobs  NormCpu  Sys7Jobs  Sys7Cpu
   ------  --------  -------  --------  -------
A: group1        13      313        16      266
B: group2         9      116         0        0
C: group3         0        0        11      164

Our old reporting solution could run multiple queries (with a union all), then post-process the rows to combine those with the same group name, so that:

Group   NormJobs  NormCpu  Sys7Jobs  Sys7Cpu
------  --------  -------  --------  -------
group1        13      313         0        0
group1         0        0        16      266

were merged together, along the lines of:

select groupname, sum(jobs), sum(cpu), 0, 0 from tbl
    where subsys = 'NORM'
    group by groupname
union all
select groupname, 0, 0, sum(jobs), sum(cpu) from tbl
    where subsys = 'SYS7'
    group by groupname

Unfortunately, our new solution does not allow post-processing and it all has to be done in the SQL query.

Keeping in mind that there may be groups with SYS7 rows, NORM rows, both or neither, what's the best way to achieve this?

I thought about sub-querying the table from within an outer select but that may have performance ramifications.

In addition, that would be a pain since I'd have to make the outer query include NORM and SYS7 subsystems then run a subquery for every field (I can't make the outer query just for NORM jobs since the presence of a group with only SYS7 rows wouldn't be caught that way).

Can you bods weave any of your left-middle-inner-offside-join magic to come up with a viable solution?

I'd prefer a vendor-agnostic solution but, if you must go vendor-specific, the platform is DB2. Still, other platforms may at least give me an ideas of what to try so I'm happy to see them.

Answer 1

I don't understand the problem with sub-querying, it seems like it should be just as fast:

select
    sub.gn as groupname,
    sum(sub.nj) as NormJobs, sum(sun.nc) as NormCpu,
    sum(sub.sj) as Sys7Jobs, sum(sub.sc) as Sys7Cpu
  from (
      select
          groupname as gn,
          sum(jobs) as nj, sum(cpu) as nc,
          0 as sj, 0 as sc
        from tbl
          where subsys = 'NORM'
          group by groupname
        union all select
            groupname as gn,
            0 as nj, 0 as nc,
            sum(jobs) as sj, sum(cpu) as sc
          from tbl
          where subsys = 'SYS7'
          group by groupname
    ) as sub
    group by sub.gn
    order by 1