troubleshooting Question

Im trying to get a total for the following query

Avatar of jknj72
jknj72 asked on
Oracle DatabaseSQL
2 Comments1 Solution138 ViewsLast Modified:
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
Join our community to see this answer!
Unlock 1 Answer and 2 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 2 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros