Link to home
Start Free TrialLog in
Avatar of jknj72
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-2014','dd-mon-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-2014','dd-mon-rrrr'))
                          )
                      )
                    )
            )
      group by range_row
      order by range_row;
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jknj72
jknj72

ASKER

When I have a query that Im going to try and write, I post the question and then try to figure it out on my own while I wait. Im happy to say that I had almost identical code that you sent. Hey man Im getting there Slight...Thanks again as always!!