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.