Query Report

[Bobbiegirl]

I have written the following code to try and run a report that will list customers names and ytd charter costs whose ytd charter cost are greater than the average charter cost for all customers. There are 4 table involved:
customer: PK cus_code, cus_fname, cus_lname||
charter: pk char_trip, char_date, cus_code,char_distance,ac_number||
aircraft: pk ac_number, mod_code||
model: pk mod_number, mod_chg_mile
The total cost is calculated by the destination times the charge per mile plus 35% for profit. When I run it I get an error of Oracle>

@ U7LQ3
(SELECT avg(sum(charter.char_distance*model.mod_chg_mile)*1.35,'$99,999.99')
*
ERROR at line 13:
ORA-00933: SQL command not properly ended
[SELECT cust.cus_fname, cust.cus_lname "Customer",
     sum(charter.char_distance*model.mod_chg_mile)* 1.35, '$99,999.99' "C\
harter"
FROM hartmar.customer JOIN hartmar.charter
ON customer.cus_code = charter.char_code
 JOIN hartmar.aircraft
ON charter.ac_number = aircraft.ac_number
JOIN hartmar.model
ON aircraft.mod_code = model.mod_code
where exists
    (select distinct charter.char_date
      from hartmar.charter
     where charter.char_date = '12 months')
(SELECT avg(sum(charter.char_distance*model.mod_chg_mile)*1.35,'$99,999.9\
9')
   FROM hartmar.charter
GROUP BY customer.cus_lname)
;]

Can someone please advise, I am beginning to think that I am trying to over analyze it.

Thank you
Barb

[jwenting]

yes, you have a syntax error in your SQL which is why Oracle doesn't understand it.
Most likely it's a missing or superfluous brace somewhere.

[Bobbiegirl]

I have posted the whole srcipt again as I have made a ouple of adjustments. Also the error I am getting is on the end of the script:

{ERROR:(SELECT avg(sum(charter.char_distance * model.mod_chg_mile)*1.35,'$99,999.99'))
 *
ERROR at line 13:
ORA-00936: missing expression}

new updated script:

[SELECT cust.cus_fname, cust.cus_lname "Customer",
     sum(charter.char_distance*model.mod_chg_mile)* 1.35, '$99,999.99' "Charter"
FROM hartmar.customer JOIN hartmar.charter
ON customer.cus_code = charter.char_code
 JOIN hartmar.aircraft
ON charter.ac_number = aircraft.ac_number
JOIN hartmar.model
ON aircraft.mod_code = model.mod_code
where exists
    (select distinct charter.char_date
      from hartmar.charter
where charter.char_date = ADD_MONTHS(TO_DATE(CONCAT('10-01', 'FISCAL_YEAR'), 'MM-DD-YYYY'), -12)
(SELECT avg(sum(charter.char_distance * model.mod_chg_mile)*1.35,'$99,999.99'))
   FROM hartmar.charter
 FROM hartmar.charter
GROUP BY customer.cus_lname))
;]

I have tried to firgur out what express is missing but cannot seem to locate it.

Barb