SQL sub query

[asian_al]

mytable1 contains the customers information (phone, address, first name, customer code, last name...)
mytable2 contain the flights (charter, date, destination,customer code...)
They are joined by the customer code.
My goal is to print names of customers that have more than the average number of charters

SELECT
cu.cus_fname,
cu.cus_lname,
COUNT(ch.char_trip) ch
FROM mytable1.customer cu JOIN mytable2.charter ch
ON cu.cus_code = ch.cus_code
WHERE ch.char_trip >
(SELECT
AVG(ch.char_trip)
FROM mytable2.charter ch
where ch.cus_code = cu.cus_code)
;

Oracle>@ 5
cu.cus_fname,
*
ERROR at line 2:
ORA-00937: not a single-group group function

???

[MeSampath]

Hi,

I think you can play with Group by and having options in SQL queries.

You can give a try with following way

SELECT
cu.cus_fname,
cu.cus_lname,
COUNT(ch.char_trip) ch_cnt
FROM customer cu JOIN charter ch
ON cu.cus_code = ch.cus_code
group by cu.cus_code having avg(ch.char_trip) > ch_cnt

Let me know if this helps.

Good luck.