jknj72
asked on
Im trying to get a total for the following query
I reaize that its just counting for every row but is there a way I can put a Select around this to get the SUM of nbr_risks or even better to include the SUM in the resultset ?
select sum(prem) sum_prem
, count(1) nbr_risks --Trying to get a total of this
, range_row from
( select prem
, case when (nvl(prem,0) <= 499) then 1
when (nvl(prem,0) <= 999) then 2
when (nvl(prem,0) <= 4999) then 3
else 4
end as range_row
from (
SELECT nvl(per.tot_est_std_prem,0 ) prem --supposed to include exp const and PPAP, but not second injury or uninsur emp surcharges
from coverage cov inner join cov_period per on (cov.carrid = per.carrid and cov.polnbr = per.polnbr and cov.cov_effdt = per.cov_effdt)
where cov.plan_ind = 2
and ( (cov.status in ('A','R','E','N')
and cov.cov_expdt between trunc(TO_DATE('01-SEP-2014 ','dd-mon- rrrr'), 'mm') and last_day(TO_DATE('01-SEP-2 014','dd-m on-rrrr')) )
or (cov.status = 'C' and cov.status_dt > cov.cov_effdt
and cov.status_dt between trunc(TO_DATE('01-SEP-2014 ','dd-mon- rrrr'), 'mm') and last_day(TO_DATE('01-SEP-2 014','dd-m on-rrrr'))
)
)
)
)
group by range_row
order by range_row;
select sum(prem) sum_prem
, count(1) nbr_risks --Trying to get a total of this
, range_row from
( select prem
, case when (nvl(prem,0) <= 499) then 1
when (nvl(prem,0) <= 999) then 2
when (nvl(prem,0) <= 4999) then 3
else 4
end as range_row
from (
SELECT nvl(per.tot_est_std_prem,0
from coverage cov inner join cov_period per on (cov.carrid = per.carrid and cov.polnbr = per.polnbr and cov.cov_effdt = per.cov_effdt)
where cov.plan_ind = 2
and ( (cov.status in ('A','R','E','N')
and cov.cov_expdt between trunc(TO_DATE('01-SEP-2014
or (cov.status = 'C' and cov.status_dt > cov.cov_effdt
and cov.status_dt between trunc(TO_DATE('01-SEP-2014
)
)
)
)
group by range_row
order by range_row;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER