During one of my projects, I landed up using a UNION ALL to get the resultset. However, UNION ALL gave 2 records of the 2 separate queries. We wanted to combine these 2 records of union all into one single record.
This is how I could achieve it:
This is how I could achieve it:
select
sub.gn as groupname,
sum(sub.nj) as AJobs, sum(sun.nc) as ACnt,
sum(sub.sj) as BJobs, sum(sub.sc) as BCnt
from (
select
groupname as gn,
sum(jobs) as nj, sum(cnt) as nc,
0 as sj, 0 as sc
from tbl
where type= 'A'
group by groupname
union all select
groupname as gn,
0 as nj, 0 as nc,
sum(jobs) as sj, sum(cnt) as sc
from tbl
where subsys = 'B'
group by groupname
) as sub
group by sub.gn
order by 1
where tbl is a table with fields type,groupname,jobs and count.
The query basically gets the total jobs and count for type = A,B for all groups.
No comments:
Post a Comment