0

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

???

2
Contributors
1
Reply
2
Views
7 Years
Discussion Span
Last Post by MeSampath
0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.