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;
Oracle DatabaseSQL

Avatar of undefined
Last Comment
jknj72

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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!!
Your help has saved me hundreds of hours of internet surfing.
fblack61