oracle query - count where percent is greater than 100

select a.area_rep_no,
          a.drive_date,
          a.drive_id,          
          a.site_code,
          a.projection,
          nvl(a.actual_draw,0) actual_draw,      
          to_char(round((nvl(a.actual_draw,0) / a.projection) * 100 ),'fm9999.00') as pert,
          CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
         END coach_drive
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-may-2013' and '31-may-2013'
  and a.area_rep_no = 12
order by 1,2 ,4

------------------------

AREA_REP_NO|DRIVE_DATE|DRIVE_ID|SITE_CODE|PROJECTION|ACTUAL_DRAW|PERT|COACH_DRIVE
12|5/1/2013|2019509|AL05|21|19|90.00|Coach
12|5/1/2013|2019058|EP01|94|82|87.00|Inside
12|5/1/2013|2020841|HL11|7|3|43.00|Mini
12|5/5/2013|2019536|KK04|26|21|81.00|Coach
12|5/5/2013|2021500|PH26|31|19|61.00|Inside
12|5/6/2013|2019537|BV06|21|15|71.00|Mini
12|5/7/2013|2019544|HD04|21|30|143.00|Inside
12|5/8/2013|2021889|BV10|24|23|96.00|Inside
12|5/8/2013|2019549|BY03|10|11|110.00|Coach
12|5/9/2013|2021793|BV12|6|2|33.00|Mini
12|5/14/2013|2019470|AL36|21|19|90.00|Coach
12|5/15/2013|2019562|BY07|26|17|65.00|Coach
12|5/15/2013|2019511|HS01|31|41|132.00|Inside
12|5/15/2013|2019592|KK08|19|16|84.00|Coach
12|5/15/2013|2019555|SU12|8|5|63.00|Mini
12|5/16/2013|2019517|HS01|31|32|103.00|Inside
12|5/17/2013|2019391|CI04|22|17|77.00|Inside
12|5/20/2013|2019451|CU09|15|16|107.00|Mini
12|5/22/2013|2019650|EP11|63|84|133.00|Inside
12|5/23/2013|2018861|KK11|30|26|87.00|Inside
12|5/25/2013|2021808|KK16|20|19|95.00|Inside

My requirement is - I need a count where the percent(pert) is > 100 %
LVL 6
anumosesAsked:
Who is Participating?
 
Geert GruwezConnect With a Mentor Oracle dbaCommented:
wrap the select in a select

select count(*) from (
select a.area_rep_no,
          a.drive_date,
          a.drive_id,          
          a.site_code,
          a.projection,
          nvl(a.actual_draw,0) actual_draw,      
          to_char(round((nvl(a.actual_draw,0) / a.projection) * 100 ),'fm9999.00') as pert,
          CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
         END coach_drive
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-may-2013' and '31-may-2013'
  and a.area_rep_no = 12
order by 1,2 ,4)
where pert > 100

Open in new window

0
 
awking00Connect With a Mentor Commented:
select ...,
sum(case when nvl(a.actual_draw,0)/a.projection > 1 then 1 else 0 end) as count100%+
0
 
awking00Connect With a Mentor Commented:
If pert can never be >= 200%, then
select ...,
floor(nvl(a.actual_draw,0)/a.projection) as count100%+
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
awking00Commented:
BTW, the problem with pert > 100 is that it's a character field when applying the to_char function so, for example, '90' will be greater than '100'
0
 
anumosesAuthor Commented:
I slightly changes the query

select count(*) from (
select a.area_rep_no,
          a.drive_date,
          a.drive_id,          
          a.site_code,
          a.projection,
          nvl(a.actual_draw,0) actual_draw,      
          to_char(round((nvl(a.actual_draw,0) / a.projection) * 100 ),'fm9999.00') as pert,
              (nvl(a.actual_draw,0) / a.projection) * 100  as pert1,
          CASE
            WHEN coach_drive='C' THEN 'Coach'
            WHEN coach_drive='M' THEN 'Mini'
            ELSE 'Inside'
         END coach_drive
  from blood_drives a,
       sites b
where a.site_code = b.site_code
  and a.projection != 0
  and a.drive_cancelled is null
  and a.drive_date between '01-may-2013' and '31-may-2013'
  and a.area_rep_no = 12
order by 1,2 ,4)
where pert1 > 100
0
 
awking00Commented:
There is no need to replicate the whole query if you only wanted the count and not the other attribute values, why not just -
select sum(case when pert > 1 then 1 else 0 end) as cnt from
(select nvl(a.actual_draw,0)/a.projection pert
 from blood_drives a, sites b where a.site_code = b.site_code
 and a.projection != 0 and a.drive_cancelled is null
 and a.drive_date between '01-may-2013' and '31-may-2013' and a.area_rep_no = 12)

Also, be careful with
"a.drive_date between '01-may-2013' and '31-may-2013'"
If drive_date contains a time portion, you will not get any
records for 31-may-2013 except for precisely midnight and,
when doing date math, it is always better explicitly convert
to date datatypes -
where a.drive_date >= to_date('01-may-2013',dd-mon-yyyy')
  and a.drive_date < to_date('01-jun-2013',dd-mon-yyyy')
0
 
anumosesAuthor Commented:
We are not hardcoding dates. It was as an example I gave. We are taking dates from the drives table.
0
All Courses

From novice to tech pro — start learning today.