Hello! is that anyway to write similar query without using union?

Question

is that anyway to write similar query without using union?

select sum(decode(p.sumsend,0,1,0)) recvcnt,
       sum(decode(p.sumsend,0,1,0)*p.sumserv) recvsum
  from some_table p
 where p.polefilter = 5
 union
select sum(decode(p.sumsend,0,1,0)) recvcnt,
       sum(decode(p.sumsend,0,1,0)*p.sumserv) recvsum
  from some_table p
 where p.polefilter != 5

Answer 1

If you are OK with having all 4 columns on one row, then one option is conditional aggregation:

select 
    sum(case when polefilter =  5 and sumsend = 0 then 1 else 0 end) recvcnt1,
    sum(case when polefilter =  5 and sumsend = 0 then 1 else 0 end *  sumserv) recvsum1,
    sum(case when polefilter <> 5 and sumsend = 0 then 1 else 0 end) recvcnt2,
    sum(case when polefilter <> 5 and sumsend = 0 then 1 else 0 end *  sumserv) recvsum2
from some_table p
where polefilter is not null

On the other hand, if you want two rows in the resultset, then you can use aggregation and a case expression to define the groups:

select 
    case when polefilter = 5 then 1 else 0 end as polefilter_is_5
    sum(case when sumsend = 0 then 1 else 0 end) recvcnt,
    sum(case when sumsend = 0 then 1 else 0 end *  sumserv) recvsum1
from some_table p
where p.polefilter is not null
group by case when polefilter = 5 then 1 else 0 end

Note that I changed the decode() functions to case expressions; both do the same thing, but the latest is standard SQL (and is somehow more flexible).