CODEWITHSUNDEEP
sqlaverage
TitanC
TitanC

Reputation: 139

sql AVERAGE function

I am trying to get my output to display the customers who took more trips than the total average. The overall average is 2.5. Any helps/hints in the right direction would be much appreciated.

 SELECT 
       customer.cus_fname "Customer First Name",
       customer.cus_lname "Customer Last Name",
        AVG(count(charter.char_trip)) > 3 "Number of Flights"     
 FROM chartname.customer 
 LEFT JOIN chartname.charter ON customer.cus_code= charter.cus_code
 GROUP BY customer.cus_fname, customer.cus_lname;

Upvotes: 0

Views: 136

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You are looking for the having clause:

 SELECT cu.cus_fname "Customer First Name", cu.cus_lname "Customer Last Name",
        count(ch.char_trip) "Number of Flights"     
 FROM chartname.customer cu LEFT JOIN
      chartname.charter ch
      ON cu.cus_code =  ch.cus_code
 GROUP BY cu.cus_fname, cu.cus_lname
 HAVING COUNT(ch.char_trip) >= 3;

Conditions for filtering go in the where or having clauses, not in the select clause.

Upvotes: 2

Related Questions