Thursday, January 16, 2014

Combining UNION ALL to get a single record

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:
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.