How to combine two requests (union of all) in one line?

Question

I have a difficult situation trying to get information from several queries on one line.

Consider the following table:

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

containing 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 bullets on the right (for example, A1 ) are the sections in the report below that should affect each row.

I need a query that can return one row for each user group, but with one condition. The values ​​for Jobs and Cpu must go to different columns depending on the identifier of the subsystem, and I am only interested in the identifiers of the subsystem SYS7 and NORM .

So, for example, we need the following (bit A/B/1/2 is a cross-reference to the lines 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 

In our old reporting solution, you could run multiple queries (with union all ) and then process the rows to combine them with the same group name so that:

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

were combined together in rows:

 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 all this needs to be done in the SQL query.

Remember, there can be groups with SYS7 lines, NORM lines, both both, and without that, what is the best way to achieve this?

I was thinking of sub-querying the table from an external selection, but it could have performance implications.

In addition, this would be a pain, since I would have to include an external query in the NORM and SYS7 subsystems, then run a subquery for each field (I cannot make an external query only for NORM jobs, since there would be no group with only SYS7 strings caught this way).

Can you combine any of your left-middle-inner-offside connection magic to come up with a viable solution?

I would prefer a solution for agricultural technology, but if you need to go to a specific supplier, the platform is DB2. However, other platforms can at least give me an idea of ​​what to try, so I'm glad to see them.

Answer 1

I don’t understand the problem with the subquery, it seems like it should be 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 

Answer 2

This is a pivot table query. (Find this if you need more information.)

The query structure you want is as follows:

 SELECT groupname, SUM(CASE WHEN subsys = 'NORM' THEN jobs ELSE 0 END) AS NormJobs, SUM(CASE WHEN subsys = 'NORM' THEN cpu ELSE 0 END) AS NormCpu, SUM(CASE WHEN subsys = 'SYS7' THEN jobs ELSE 0 END) AS Sys7Jobs, SUM(CASE WHEN subsys = 'SYS7' THEN cpu ELSE 0 END) AS Sys7Cpu, SUM(CASE WHEN subsys NOT IN ('NORM', 'SYS7') THEN jobs ELSE 0 END) AS OtherJobs, SUM(CASE WHEN subsys NOT IN ('NORM', 'SYS7') THEN cpu ELSE 0 END) AS OtherCpu FROM ??? GROUP BY groupname 

Answer 3

This is a typical summary query — here's how you do it with a CASE statement :

  SELECT t.group, SUM(CASE WHEN t.subsys = 'NORM' THEN t.jobs ELSE NULL END CASE) AS NormJobs, SUM(CASE WHEN t.subsys = 'NORM' THEN t.cpu ELSE NULL END CASE) AS NormCpu, SUM(CASE WHEN t.subsys = 'SYS7' THEN t.jobs ELSE NULL END CASE) AS Sys7Jobs, SUM(CASE WHEN t.subsys = 'SYS7' THEN t.cpu ELSE NULL END CASE) AS Sys7Cpu FROM CPUUSAGE t GROUP BY t.group 

Unfortunately, DB2 CASE statements must end in END CASE when Oracle / SQL Server / MySQL / Postgres does not. Well, PLSQL supports END CASE ...

There is also PIVOT syntax , which is also supported in Oracle 11g and SQL Server 2005+ .